Running Macro Based On Checkbox

[jakefuller]

Hello -

I am trying to run a macro that evaluates if a box (ReverseCheckbox on the MAIN tab) is checked, and if so, run the rest of the code. What am I doing wrong? Thank you!

Sub REVERSE()


Dim ws As Worksheet
Dim LastRow As Long
Dim K As Long
        
Sheets("agility").Select
            
Set ws = ActiveSheet
LastRow = ws.Range("D" & Rows.COUNT).End(xlUp).Row


If MAIN.ReverseCheckbox.Value = False Then Exit Sub Else

For K = LastRow To 1 Step -1


If Left(ws.Range("D" & K).Value, 5) = "SWING" Then
ws.Range("D" & K).Offset(0, -2) = "=VLOOKUP(RC[1],TABLES_DOOR_SWING_REV,2,FALSE)"


End If

Next K

End Sub

[Zvoni]

Stab in the Dark: Don't use ActiveSheet
And there is an End if missing

Sub REVERSE()
Dim ws As Worksheet
Dim LastRow As Long
Dim K As Long        
    Set ws = Sheets("agility")
    LastRow = ws.Range("D" & Rows.COUNT).End(xlUp).Row
    If MAIN.ReverseCheckbox Then 
        For K = LastRow To 1 Step -1
            If Left(ws.Range("D" & K).Value, 5) = "SWING" Then
                ws.Range("D" & K).Offset(0, -2).Formula = "=VLOOKUP(RC[1],TABLES_DOOR_SWING_REV,2,FALSE)"
            End If
        Next K
    End If
End Sub

[jakefuller]

Thanks for the feedback. I'm getting an error on the "If MAIN.ReverseCheckbox then" line. "Object required." I tried changing it to "MAIN.Reversecheckbox = True" and "MAIN.ReverseCheckbox = 1" but that didnt work.

[Zvoni]

Your problem is the "MAIN"
Is the Sheet-Object called MAIN, or is the Name of the Sheet MAIN? (Those are two different things)

[jakefuller]

the name of the sheet is MAIN, the checkbox is called ReverseCheckbox

[Zvoni]

untested

Sub REVERSE()
Dim ws As Worksheet
Dim MAIN As Worksheet
Dim LastRow As Long
Dim K As Long        
    Set ws = Sheets("agility")
    Set MAIN = Sheets("Main")
    LastRow = ws.Range("D" & Rows.COUNT).End(xlUp).Row
    If MAIN.ReverseCheckbox Then 
        For K = LastRow To 1 Step -1
            If Left(ws.Range("D" & K).Value, 5) = "SWING" Then
                ws.Range("D" & K).Offset(0, -2).Formula = "=VLOOKUP(RC[1],TABLES_DOOR_SWING_REV,2,FALSE)"
            End If
        Next K
    End If
End Sub

[jakefuller]

Thank you for the response. I'm getting the error "Compile Error: Method or data member not found" and it appears to be highlighting ".ReverseCheckbox" . See attached screenshot. Am I perhaps using the wrong type of checkbox? I dont use alot of checkboxes, I added it in form control.

I just added an activeX checkbox and that gave the same error.

[Zvoni]

Ahh....Crap.
IIRC, only ActiveX-Controls can be addressed like above.
The "other" controls i'd have to look up how to address them (been a long time for me...)

[westconn1]

the code will probably work if the code is on the codepane of the MAIN worksheet, else you would need to address the checkbox as an object of a shape object

if you want to post a sample workbook (zip first), with checkbox and the code that fails someone will probably be able to fix it for you

[jakefuller]

I took the easy route - I linked the checkbox to a cell and then adapted the code for that. Thanks for all the help!

 If ws.Range("C1") = "True" Then