Here's an alternative. A user form to enter the session times in.
ThisWorkbook Code. Holds the public Variant (Array) with the schedule times. Place a Break on line "Next" in Sub Test, then hover the mouse over X, Y, and i as you press F5 to cycle thru.
Option Explicit
Public SessionSchedule As Variant
Private Sub Workbook_Open()
frmSurveySessions.Show
End Sub
Public Sub test()
Dim X, Y, i
For i = LBound(SessionSchedule) To UBound(SessionSchedule)
X = SessionSchedule(i, 1)
Y = SessionSchedule(i, 2)
Next
End Sub
The UserForm Code. Note that the Close Command button calls Test.
Option Explicit
Private Sub cbutClose_Click()
ThisWorkbook.SessionSchedule = MakeSchedule
Unload Me
ThisWorkbook.test
End Sub
Private Sub tbxNumSessions_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long
For i = 2 To 16
Me.Controls("Frame" & i).Visible = False
Next i
For i = 2 To CLng(Me.tbxNumSessions.Value)
With Me.Controls("Frame" & i)
.Visible = True
.Height = 24
.Top = Me.Controls("Frame" & i - 1).Top + 24
End With
Next i
End Sub
Private Function MakeSchedule() As Variant
Dim NumSessions As Long
Dim Tmp As Variant
Dim i As Long
Dim S As Long
NumSessions = Me.tbxNumSessions.Value
ReDim Tmp(1 To NumSessions, 1 To 2)
With Me.Controls
For i = 1 To NumSessions
If .Item("Frame" & i).Visible Then
S = S + 1
Tmp(S, 1) = .Item("tbxSessionStart" & i).Value
Tmp(S, 2) = .Item("tbxSessionEnd" & i).Value
End If
Next i
End With
MakeSchedule = Tmp
End Function
Download: Survey Sessions.xlsm
This is just the basic outline. you still need to check each Time box for proper formatting, and I would use DateTime Serial values in the final array. They are much easier for all VBA and Excel to work with.
The feature to change num-sessions on the fly is built in
Some features that can be added:
- Provide a "standard session length" and "standard time between" inputs, that would automatically fill in all the time slots.
- Allow clearing the times of a session for breaks, lunches, etc