ComboBox converting Date to Number

[bcjhughes]

I'm using a ComboBox in Excel 2003 to display a list of set dates that are formatted "mm/dd/yyyy". Everything works fine but after the selection is made the value presented in the ComboBox is presented as a number. Example: user selects "10/27/2008" in the ComboBox and then it changes to 39748, the number that represents that date. Does someone know a setting on ComboBox that could fix this or a way to trick it not to do this?

Thanks,

[Bob Phillips]

I am not clear what or how this happens. What sort of combo, and what is the code controlling it.

[bcjhughes]

Here is an example of what I'm talking about. What I need it to do is leave the date formatted as it is in the listfillrange.

I think what is needed is something like this to change the number back to the date format, but this specific example doesn't work.


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

ComboBox1.Value = "39714".

Does anyone know a function that would change the date string("39714") to a proper date?

[Bob Phillips]

Take a look at this

[bcjhughes]

okay this workaround will fix the issue, but it's not that practical, particularily when you want to trigger other things off the change event. Anyone have anything more elegant?

Private Sub ComboBox1_Change()
ComboBox1.Value = CStr(CDate(ComboBox1.Value))
End Sub

[bcjhughes]

Thanks, like your solution better!