Problems with apostrophe and fill down dates 1

[laurafinn]

I have put a combo button on a form which I am trying to use to look up a data from a worksheet. So I have a column called colDate which contains all the dates and I link this to the Combo button. When I use the combo button to select a date, the date shown in the combo button turns out to be a number and not the date - almost as if the combo button has been formatted to only accept number s i.e it has converted the date selected into a number. Please can any one help

It has been suggested that I put Apostrophe in front of all the dates so that this will display the date in teh combo - but I have uesd teh fill down to add dates to the automatically and now the fill down doesn't work

e.g.

'1/1/2003
'2/1/2003

fill down gives
'1/1/2003
'2/1/2003
'1/1/2003
'2/1/2003 please can some one help further

 

[sawedoff]

I don't know which version of Excel you are running, but in 2000, if you are using the Combo box from the Control toolbox, the date should show up as a date, even without the apostrophe.

If you have used the Data-Validation-List method, all you will need to do is format the cell as a date. Then you don't need the apostrophe in front of the dates, and your fill should work. Give the Format-Cells-Number-Date (choose one) a try.


Sawedoff

 

[SkipVought]

laura,

Do NOT put tics in front of your dates -- the will NOT BE DATES!

Dates are NUMBERS like 37812.

This code produces a correct result (using Clng to convert the string "37812" to a date value

Private Sub ComboBox1_Change()
    [a1] = Application.Match(CLng(ComboBox1.Text), Range("DateList"), 0)
End Sub

Skip,

http://www.TheOfficeExperts.com

 

[Zathras]

In Excel 97 you can do this:

Set the RowSource property to a parallel range of formulas keyed to your dates. E.g., if your dates are in B3:B15, put this formula in C3:
[blue]

  =TEXT(B3,"m/d/yy")

[/color]
and copy down thru C15. Then set the RowSource property to C3:C15.

 

[laurafinn]

I have put a combo button on a form which I am trying to use to look up a data from a worksheet. So I have a column called colDate which contains all the dates and I link this to the Combo button. When I use the combo button to select a date, the date shown in the combo button turns out to be a number and not the date - almost as if the combo button has been formatted to only accept number s i.e it has converted the date selected into a number. Please can any one help

It has been suggested that I put Apostrophe in front of all the dates so that this will display the date in teh combo - but I have uesd teh fill down to add dates to the automatically and now the fill down doesn't work

e.g.

'1/1/2003
'2/1/2003

fill down gives
'1/1/2003
'2/1/2003
'1/1/2003
'2/1/2003 please can some one help further

 

[SkipVought]

1) take the tics off your dates!

2) include this statement in the click event

Private Sub ComboBox1_Click()
    With ComboBox1
       .Value = Format(.Value, "mm/dd/yyyy")
    End With
End Sub

VOLA!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884