none
Issues with For Loop That Highlights excel cells RRS feed

  • Question

  • I wrote a code in excel that allows you to check boxes next to line items and when you do so it highlights the checkbox green and when you deselect it removes the highlight back to white space. It works perfectly fine however I wrote a manual code for every checkbox. I'm trying to condense the code into a for loop since there are over 70 checkboxes within the document.

    This is what I originally had

    Private Sub CheckBox70_Click() 'Arrow Board'
    Worksheets("Sheet1").Range("E113") = CheckBox70
    If Sheets("Sheet1").OLEObjects("CheckBox70").Object.Value = True Then
        Range("A113:C113").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        End If
        If Sheets("Sheet1").OLEObjects("CheckBox70").Object.Value = False Then
        Range("A113:C113").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 1
            .PatternTintAndShade = 0
        End With
        End If
    
    End Sub

    and I tried to introduce a for loop to execute 3 checkboxes to test it out and came up with this

    Sub For_Loop_one()
        Dim LCounter As Integer
        For LCounter = 68 To 70
            LCounter2 = 111 To 113
                CheckBoxLCounter_Click()
                Worksheets("Sheet1").Range("LCounter2") = CheckBoxLCounter
                If Sheets("Sheet1").OLEObjects("CheckBoxLCounter").Object.Value = True Then
                    Range("ALCounter2:CLCounter2").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent6
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                End If
                If Sheets("Sheet1").OLEObjects("CheckBoxLCounter").Object.Value = False Then
                    Range("ALCounter2:CLCounter2").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent6
                        .TintAndShade = 1
                        .PatternTintAndShade = 0
                    End With
                End If
            Next LCounter2
        Next LCounter
    End Sub
    

    This is the only project I've done with VBA so its difficult for me to diagnose the problem, it just says syntax error. The LCounter Variable is the checkbox numbers and the LCounter2 variable is the number of the cell row that the checkboxes are located in

    Wednesday, August 1, 2018 5:29 PM

All replies

  • I think I made some progress but its possible I went in the wrong direction... I think the main problem now is that It doesnt seem to let me call out the specific checkbox based on the variable I defined in the loop

    Sub For_Loop_one()
        Dim LCounter As Integer
        Dim LCounter2 As Integer
        For LCounter = 68 To 70
            For LCounter2 = 111 To 113
                For LCounter3 = 1 To 3
                    CheckBoxLCounter_Click()
                    Worksheets("Sheet1").Range("LCounter2") = CheckBoxLCounter
                    If Sheets("Sheet1").OLEObjects("CheckBoxLCounter").Object.Value = True Then
                        Cells(LCounter3, LCounter2).Select
                        With Selection.Interior
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .ThemeColor = xlThemeColorAccent6
                            .TintAndShade = 0
                            .PatternTintAndShade = 0
                        End With
                    End If
                    If Sheets("Sheet1").OLEObjects("CheckBoxLCounter").Object.Value = False Then
                        Cells(LCounter3, LCounter2).Select
                        With Selection.Interior
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .ThemeColor = xlThemeColorAccent6
                            .TintAndShade = 1
                            .PatternTintAndShade = 0
                        End With
                    End If
                Next LCounter3
            Next LCounter2
        Next LCounter
    End Sub

    Wednesday, August 1, 2018 5:44 PM
  • Hello Hal Reigi,

    It seems that you want to check a checkbox to change the interior of a range. How do we know what's the range associated with the checkbox?  For instance, if we click CheckBox70, it will change A113:C113. What about CheckBox69, which range will be changed? What's their regular?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 2, 2018 5:22 AM