Combo Box Display

[bob leathers]

Hello Folks

Using VB 6.3
I have a combo box in a user for for selecting certain dates from the
spreadsheet. The drop down works ok, but when I select a date the
information displayed in the window is not the date.

For instance I select 02/09/07 and the displayed value is 39122.
How do I get the window to display 02/09/07 as opposed to 39122.

I have the same problem with a time combo box.

Thanks for your help

 

[Guest]

hi,
39122 is the date number for 2/9/07.
in your code you need to pre-format the cell to date. it should display date
then.

Regards
FSt1

 

[Dave Peterson]

How are you adding the items to the combobox?

Maybe it would be better to use something like:
me.combobox1.additem format(whereeveryougetthedate, "mm/dd/yyyy")

If this isn't sufficient, post back with some more details.

Is the combobox on a userform -- or on a worksheet. And if it's on a worksheet,
did you use the combobox from the control toolbox toolbar or from the dropdown
toolbar?

 

[bob leathers]

BTW I am also using Excel 2003
I am adding the data to a userform combo box from Cells in a spreadsheet
(in combo box properties Row Source AA2:AA12). The cell Formats in the
spreadsheet are "mm/dd/yy". When the user opens the form to add the date
this data is displayed properly in the dropdown. When you select the
date you want the serial date is substituted in the combo box window. I
need to format the combo box to a "mm/dd/yy" format but don't know how.

The same applies to the time function in another combo Box. Drpo down
displays properly but serial time is displayed. I need to format this to
a "hh:mm" format but again don't know how

Thanks

 

[Dave Peterson]

Drop the rowsource and use .additem inside the userform_initialize event.


Option Explicit
Private Sub UserForm_Initialize()
Dim myCell As Range
For Each myCell In Worksheets("sheet999").Range("aa2:aa12").Cells
Me.ComboBox1.AddItem myCell.Text 'or format(mycell.value,"mm/dd/yyyy")
Next myCell
End Sub

And do the same thing with the times.

 

[bob leathers]

This worked Great!!!!!

Thanks