format of combobox on userform

[red6000]

Hi I have a combobox where the data is dates and a second where it is time.

For the dates combox, the list happily shows the dates in my required format
(ie dd mmm yyyy), but when you choose a date from the list it appears in the
box in the serial date format (ie 39123).

A similar thing happens with times. If I choose 08:00 from the list, it
shows as 0.33333333 (ie 8/24).

Is there a way I can format the visible value of the chosen item from the
combobox list so that it appears as it does in the list?

Many thanks.

 

[Guest]

In the VBA code editor window, try using this for the dates combo box:

Private Sub ComboBox1_Change()

ComboBox1.Value = Format(ComboBox1.Value, "m/d/yyyy")

End Sub


Likewise, for the times combo box try this:

Private Sub ComboBox2_Change()

ComboBox2.Value = Format(ComboBox2.Value, "h:mm")

End Sub

Hopefully this helps,
James

 

[NickHK]

With the code below, there is no reformatting of the displayed text, so show
the code that fills the combos and any formatting you do.

Private Sub UserForm_Initialize()
With ComboBox1
.AddItem Now()
.ListIndex = 0
End With
End Sub

Private Sub ComboBox1_Click()
With ComboBox1
Debug.Print "Text :" & .Text & vbNewLine & "Value: " & .Value
End With
End Sub

********* Debug Output *******
Text :19/07/06 11:19:08 AM
Value: 19/07/06 11:19:08 AM