Format issue

[hayden_pinto]

I have made a Userform where in one of the drop downs allows the user to select the time

Eg

Request Time - 12:30:00 AM but when the data is selected the drop down shows 0.0208333333333333

which is the wrong format

in the excel sheet i have put the right format as below

Request Time

12:00:00 AM

12:30:00 AM

1:00:00 AM


Below is the VB code in using


Private Sub UserForm_Initialize()

Label7.Caption = Format(Now(), "dd-MMM-YYYY")

Label8.Caption = Format(Now(), "hh:mm:ss")

ComboBox1.SetFocus

ComboBox1.RowSource = "=Sheet1!b2:b7" 'Process

ComboBox4.RowSource = "=Sheet1!E2:E5" 'Update

ComboBox5.RowSource = "=Sheet1!F2:F49" 'Time


Please help me get the right format for the time in the drop down

 

[Hui]

Hayden

Times in Excel range between 0 (Midnight) and 1 (Midnight 24 hours latter)

A number like: =0.0208333333333333

is in fact =0.0208333333333333*60*60*24 seconds of a day

=1800 seconds

=30 Minutes

So 0.0208333333333333 is representing 12:30am


Select the range that is the source of the Combo Box and set a Custom Number Format of HH:MM am/pm

 

[Luke M]

I think you can use the Change event to force the data back into the correct format, like this:

[pre]

Private Sub ComboBox1_Change()
ComboBox1.Text = Format(ComboBox1.Text, "dd-MMM-YYYY")
End Sub

[/pre]

 

[hayden_pinto]

Tryed this but its giving an error


Private Sub ComboBox5_Change()

ComboBox5.Text = Format(ComboBox5.Text, "dd-MMM-YYYY")

End Sub


i have to define a source for the time to be pulled from excel , how can i do that


the source data is in Sheet1 Range F1 to F49

 

[Luke M]

I think your source is okay. When I setup a dummy form on my machine, the drop down shows the date correctly in the pull down menu, but it's once a selection is made that the VB portion takes over and coverts the value to a basic number (don't want). As for the error, I apologize...I think that since the macro is changing the box, it's causing an infinite run error. Try this:

[pre]

Private Sub ComboBox5_Change()
Application.EnableEvents = False
ComboBox5.Text = Format(ComboBox5.Text, "dd-MMM-YYYY")
Application.EnableEvents = True
End Sub

[/pre]

 

[greg.begin]

try changing your "dd-mmm-yyyy" to "hh:mm:ss" and change your

procedure to click and not change.

it works for me.


private sub combobox5_click()

combobox5.text = format(combobox5.text, "hh:mm:ss")

end sub

 

[hayden_pinto]

Solution i used is that i put the time as Text only not as time