Storing times in an array

[spovolny]

Hi -
I'm looking for a way to store times in an array in order to populate
a combo box with every half hour during the day. Is this possible?
Right now, I've entered the times as strings, for example: 8:00a,
8:30a, .....etc. This is in a user form. However, when I want to
write the selected time to my Excel sheet, I need it to recognize it
as a time. Can anyone help with this? Thanks alot,
Steve

 

[Bernie Deitrick]

Steve,

No need to create an array:

Sub TimesInComboBox()
Dim i As Integer

With UserForm1.ComboBox1
.Clear
For i = 0 To 47
.AddItem Format(i / 48, "hh:mm:ss")
Next i
End With

Load UserForm1
UserForm1.Show
End Sub


HTH,
Bernie
MS Excel MVP

 

[Chip Pearson]

You don't necessarily need an array. You can use AddItem to add each time to
the combobox one at a time.

Dim StartTime As Date
Dim EndTime As Date
Dim OneTime As Date
StartTime = TimeValue("08:00:00 AM")
EndTime = TimeValue("05:01:00 PM") ' note 01 minute to handle rounding
Me.ComboBox1.Clear
For OneTime = StartTime To EndTime Step TimeSerial(0, 30, 0)
Me.ComboBox1.AddItem Format(OneTime, "hh:mm:ss AMPM")
Next OneTime


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[spovolny]

You don't necessarily need anarray. You can use AddItem to add eachtimeto
the combobox one at atime.

Dim StartTime As Date
Dim EndTime As Date
Dim OneTime As Date
StartTime = TimeValue("08:00:00 AM")
EndTime = TimeValue("05:01:00 PM") ' note 01 minute to handle rounding
Me.ComboBox1.Clear
For OneTime = StartTime To EndTime Step TimeSerial(0, 30, 0)
Me.ComboBox1.AddItem Format(OneTime, "hh:mm:ss AMPM")
Next OneTime

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consultingwww.cpearson.com
(email on the web site)

Perfect - both options work, thanks gentlemen!!

 

[spovolny]

You don't necessarily need anarray. You can use AddItem to add eachtimeto
the combobox one at atime.

Dim StartTime As Date
Dim EndTime As Date
Dim OneTime As Date
StartTime = TimeValue("08:00:00 AM")
EndTime = TimeValue("05:01:00 PM") ' note 01 minute to handle rounding
Me.ComboBox1.Clear
For OneTime = StartTime To EndTime Step TimeSerial(0, 30, 0)
Me.ComboBox1.AddItem Format(OneTime, "hh:mm:ss AMPM")
Next OneTime

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consultingwww.cpearson.com
(email on the web site)

Hey guys -
One more question, now that I've got that combo box populated with
every half hour. I'm doing some validation to make sure that the
start time (combo box) if changed is earlier than the end time
(another combo box). In other words, if the user selects 10:00AM for
the start time, and 9:00AM for the end time, it should spring an error
box. When I use the property combobox1.value to compare to
combobox2.value, it only looks at the numbers, and not the AM versus
PM. Is there another property to use so that it will actually compare
them as times? Currently, it thinks that 02:00PM is earlier than
11:00AM because 2 is less than 11.
My code is:

If cboEndTime.Value < cboStartTime.Value Then
MsgBox("Start time must be earlier than the end time.")
cboEndTime = cboStartTime.Value
End If

Thanks a ton!

 

[Bernie Deitrick]

Try

If TimeValue(cboEndTime.Value) < TimeValue(cboStartTime.Value) Then

HTH,
Bernie
MS Excel MVP