This is my userform and this is my code, when selecting a name from the Player name box I get a runtime error I am unsure how to fix. The debug shows me this line but I cannot figure out why.
I will post a screenshot of my spreadsheet, we are selecting a name, checking the tickboxes to select times from 00:00 through 23:00 and updating availability in columns J to N.
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim i As Integer
Set ws = ThisWorkbook.Sheets("Round 5")
' Clear the ComboBox
cmbPlayers.Clear
' Populate ComboBox with player names
For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
cmbPlayers.AddItem ws.Cells(i, 1).Value
Next i
End Sub
Private Sub cmbPlayers_Change()
Dim ws As Worksheet
Dim playerName As String
Dim i As Integer
Dim timeSlots As Variant
Dim dayColumns As Variant
Dim dayIndex As Integer
Dim j As Integer
Set ws = ThisWorkbook.Sheets("Round 5")
playerName = cmbPlayers.Value
' Define the columns for each day
dayColumns = Array(12, 13, 14, 15, 16) ' Columns L to P (Wednesday to Sunday)
' Clear all CheckBoxes
ClearCheckBoxes
' Find the player row and populate the CheckBoxes
For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
If ws.Cells(i, 1).Value = playerName Then
For dayIndex = 0 To UBound(dayColumns)
timeSlots = Split(ws.Cells(i, dayColumns(dayIndex)).Value, ", ")
For Each timeSlot In timeSlots
For j = 0 To 23 ' 24 time slots
If Me.Controls("chk" & dayIndex & j).Caption = timeSlot Then
Me.Controls("chk" & dayIndex & j).Value = True
End If
Next j
Next timeSlot
Next dayIndex
Exit For
End If
Next i
End Sub
Private Sub ClearCheckBoxes()
Dim i As Integer
Dim dayIndex As Integer
For dayIndex = 0 To 4 ' Wednesday to Sunday
For i = 0 To 23 ' 24 time slots
Me.Controls("chk" & dayIndex & i).Value = False
Next i
Next dayIndex
End Sub
Private Sub btnUpdate_Click()
Dim ws As Worksheet
Dim playerName As String
Dim i As Integer
Dim timeSlots As String
Dim dayColumns As Variant
Dim dayIndex As Integer
Dim j As Integer
Dim found As Boolean
Set ws = ThisWorkbook.Sheets("Round 5")
playerName = cmbPlayers.Value
found = False
' Define the columns for each day
dayColumns = Array(12, 13, 14, 15, 16) ' Columns L to P (Wednesday to Sunday)
' Find the player row and update the availability times
For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
If ws.Cells(i, 1).Value = playerName Then
For dayIndex = 0 To UBound(dayColumns)
timeSlots = ""
For j = 0 To 23 ' 24 time slots
If Me.Controls("chk" & dayIndex & j).Value = True Then
If timeSlots <> "" Then
timeSlots = timeSlots & ", "
End If
timeSlots = timeSlots & Me.Controls("chk" & dayIndex & j).Caption
End If
Next j
ws.Cells(i, dayColumns(dayIndex)).Value = timeSlots
Next dayIndex
found = True
Exit For
End If
Next i
If found Then
MsgBox "Availability updated successfully.", vbInformation
Else
MsgBox "Player not found.", vbExclamation
End If
End Sub
Sub ShowUserForm()
UserForm1.Show
End Sub