Excluding rows or columns from Checkbox macro

Issue

So, I have based a lot of functions on a sheet on checkboxes.
It grew beyond the original scope, and now I needed to add a few more control textboxes
The problem is, I wrote a script which would Unmark blank checkboxes if a cell in the row was blank (the cell in G column), and now that I have a few more checkboxes to control other functions, my script unfortunately affects the others.
Here is the script:

Sub UnMarkBlankCheckBoxes()

Dim chk As CheckBox
Dim ws As Worksheet

Set ws = ActiveSheet

For Each chk In ws.CheckBoxes
    If ws.Range("G" & chk.TopLeftCell.Row).Value = vbNullString Then
        chk.Value = False
    Else
        chk.Value = True
    End If
Next chk

End Sub

I need to be able to either select within a specific range (only column E), OR Exclude everything else. I have been trying things, but cant get it to work. Any ideas? Thanks in advance!

Solution

For multiple columns use >= and <= in connection with And

For Each chk In ws.CheckBoxes
    If chk.TopLeftCell.Column >= 3 And chk.TopLeftCell.Column <= 5 Then
        If ws.Range("G" & chk.TopLeftCell.Row).Value = vbNullString Then
           chk.Value = False
        Else
            chk.Value = True
        End If
    End If
Next chk

Or something like

If chk.TopLeftCell.Column = 3 Or chk.TopLeftCell.Column = 4 Or chk.TopLeftCell.Column = 6 Then

Answered By – Pᴇʜ

Answer Checked By – David Marino (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.