Class Module code for loop to run when any checkbox is clicked

Question

This is ongoing from a previous thread where I wanted to execute code for whenever ANY checkbox is clicked. jsotola was kind enough to help me define the trigger event. Here is a link to that thread: Event triggered by ANY checkbox click

Now, I'm trying to modify the ChkBoxGroup_Click() Sub in the class module to suit more specific needs. I've tested the code in a separate workbook as a standalone Sub that I can manually execute with F5, which works exactly as I hoped. Since I want it to run with checkbox clicks, I thought I could just paste it into the class module, but I'm getting an error I'm hoping someone could help with.

Here's what i have in a class module called: "ChkClass" Option Explicit

Public WithEvents ChkBoxGroup As MSForms.CheckBox

Private Sub ChkBoxGroup_Change()
Debug.Print "ChkBoxGroup_Change"
End Sub

Private Sub ChkBoxGroup_Click()
Dim findrow As Long, findrow2 As Long
findrow = Range("B:B").Find("Feature Styles", Range("B1")).Row
findrow2 = Range("B:B").Find("Feature Options", Range("B" & findrow)).Row
For i = findrow To findrow2

    If Range("B" & i).Value = Range("O" & i).Value Then
        Range("C" & i).Value = True
    Else: Range("C" & i).Value = False
    End If
Next i
End Sub

When I click a checkbox, I get an error box saying "compile error: Variable not defined". it also highlights the "i" in this line:

For i = findrow To findrow2

It works great in my other sheet as a standalone sub, which is as follows:

Sub FeatureStyles_TorF()
Dim findrow As Long, findrow2 As Long
findrow = Range("B:B").Find("Feature Styles", Range("B1")).Row
findrow2 = Range("B:B").Find("Feature Options", Range("B" & findrow)).Row
For i = findrow To findrow2

    If Range("B" & i).Value = Range("O" & i).Value Then
        Range("C" & i).Value = True
    Else: Range("C" & i).Value = False
    End If
Next i
End Sub

Any ideas why I'm getting the error?

score 0 · Accepted Answer · answered Oct 03 '17 at 15:22

0

Deal with the error as it says, it says you haven't defined i, so let's define it. Change:

Dim findrow As Long, findrow2 As Long

To:

Dim findrow As Long, findrow2 As Long, i as long

answered Oct 03 '17 at 15:22

Zerk

1,595
9
16

Beautiful! Rookie move! (I'm a rookie). Thanks a bunch! Works great! – csmith222 Oct 03 '17 at 23:44
Just as a bit of background to the "why". You'd get away with this in a module normally, standard procedure is if you use a variable you haven't defined excel will do it for you and create a variant to handle it (This behaviour is stopped by putting option explicit at the top of the module, which you seem to have on the class module). – Zerk Oct 04 '17 at 09:14
Did not know that! Learning more and more about this stuff, thanks to the kindness of people like you! Thanks – csmith222 Oct 04 '17 at 18:04

Class Module code for loop to run when any checkbox is clicked

1 Answers1