Results 1 to 10 of 10

Thread: Running Macro Based On Checkbox

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2014
    Posts
    28

    Running Macro Based On Checkbox

    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!

    Code:
    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

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,066

    Re: Running Macro Based On Checkbox

    Stab in the Dark: Don't use ActiveSheet
    And there is an End if missing
    Code:
    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
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2014
    Posts
    28

    Re: Running Macro Based On Checkbox

    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.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,066

    Re: Running Macro Based On Checkbox

    Your problem is the "MAIN"
    Is the Sheet-Object called MAIN, or is the Name of the Sheet MAIN? (Those are two different things)
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jul 2014
    Posts
    28

    Re: Running Macro Based On Checkbox

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

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,066

    Re: Running Macro Based On Checkbox

    untested
    Code:
    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
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jul 2014
    Posts
    28

    Re: Running Macro Based On Checkbox

    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.

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,066

    Re: Running Macro Based On Checkbox

    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...)
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,577

    Re: Running Macro Based On Checkbox

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jul 2014
    Posts
    28

    Re: Running Macro Based On Checkbox

    I took the easy route - I linked the checkbox to a cell and then adapted the code for that. Thanks for all the help!
    Code:
     If ws.Range("C1") = "True" Then

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width