xl97 userform combobox time

[Pete]

How do I write a routine (xl97) to fill a combo box on a userform with
the time starting at 12:00 AM and ending at 11:45 PM?

I populated a combo box from the times on a worksheet and wrote this. It
works. (cells A1 thru A96 have 12:00 AM, 12:15 AM, etc...)

Sub build_combobox1()

Dim x As Date

With Worksheets("Sheet3")
For x = 1 To 96
ComboBox1.AddItem .Cells(x, 1)
Next x
End With

End Sub

But I rather build the data instead of getting it. Something like this
which does not work. (the value of x I can't figure out)

Sub build_combobox1()

Dim x As Date

For x = 1200 To 2400 Step 15
ComboBox1.AddItem Format(x, "hh:mm AMPM")

Next x

End Sub

 

[Peter T]

Hi Pete,

There are 96 quarter hours per day, starting at 0 or midnight:

Sub test()
Dim i As Long
Dim q As Date
Dim t As Date

q = 1 / 96
t = 0

For i = 0 To 95
Debug.Print t, Format(t, "hh:mm AMPM")
t = t + q
Next

End Sub

You could declare q & t as Single or Double if you want time as fractions of
a day for some other purpose.

Regards,
Peter T

 

[Pete]

Thanks Peter, that is the solution to my problem.

 

[Peter T]

Also, you could add to your combo box like this:

'in a Form module containing ComboBox1
Private Sub UserForm_Initialize()
Dim i As Long
Dim q As Date
Dim t As Date
Dim asTimes(0 To 95) As String

q = 1 / 96
t = 0

For i = 0 To 95
asTimes(i) = Format(t, "hh:mm AMPM")
t = t + q
Next

Me.ComboBox1.List = asTimes()
Me.ComboBox1.ListIndex = 0

End Sub

Regards,
Peter T