So the primary problem is solved, but now I have a secondary issue related to this. I need to now take the captions of the selected checkboxes, look them up, and return a different value.
The problem seems to come from the "lookup = ...." The funny thing is, I had this working 30 minutes ago, but then excel crashed, and I wasn't able to recover any of my work. Of course, easy fix is save frequent and often, but I don't always remember to.
Private Function AddValues()
Dim i As Integer
Dim lookfor As Variant
Dim rng As Range
Dim y As Variant
i = 1
Set rng = wsLists.Range("O2:Q51")
For i = 1 To 50
Set lookfor = Me.HazardsFrm.Controls("CheckBox" & i).Caption
Set y = WorksheetFunction.VLookup(lookfor, rng, 3, False)
If Me.HazardsFrm.Controls("CheckBox" & i).Value = True Then
Call FindEmptyCell
ActiveCell.Value = y
End If
Next
End Function
So update to above, I have slightly changed the code, but also the lookup location (streamlining purposes). See below:
Private Function AddValues()
Dim i As Integer
Dim ctl As Control
Dim str As Variant
Dim y As Variant
Dim lookfor As Variant
Dim rng As Range
Set rng = wsLists.Columns("O:T")
i = 1
For i = 1 To 50
Set ctl = Me.HazardsFrm.Controls("CheckBox" & i)
Set lookfor = ctl.Caption
Set y = WorksheetFunction.VLookup(lookfor, wsLists.Columns(2), False)
Set str = WorksheetFunction.VLookup(lookfor, wsLists.Columns(3), False)
If ctl.Value = True Then
Call FindEmptyCell
ActiveCell.Value = str
End If
Next
End Function