Dynamically Checking for Checkboxes with the same name as Worksheets -VBA Excel

Question

I am on excel, I have about 30 UserForm checkboxes, each of the checkboxes are located on a page called "Summary" in a big column. Each of which corresponds to a worksheet. I am attempting to loop through these checkboxes to see which are ticked and not ticked. I have put some code within a loop of all worksheets, this is the bit that is not working:

  Private Sub Run_Click()
Dim SummaryFirstBlankRow As Integer
Dim LastRow As Integer
Dim LastRowH As Integer
Dim rng As Range
Dim SumOfHours As Variant
Dim EndLoop As Boolean
Dim DupeRowNo As Integer
Dim RowNo2 As Integer
Dim ClearContent As Boolean
Dim cbtrue As Boolean
Dim ws As Worksheet


cbtrue = False
    For Each ws In ActiveWorkbook.Worksheets



        LastRowH = ws.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row


    'If ActiveWorkbook.Sheets("Summary").CheckBoxes(ws.name).Value = False Then ' looks to see if ws.names' checkbox is checked or not
     '               ClearContent = True 'if not checked, sets clear content to true
    'End If

    For Each CheckBox In Sheets("Summary").CheckBoxes
        If CheckBox.name = ws.name Then
            If CheckBox.Value = False Then
                ClearContent = True
            End If
        End If
    Next CheckBox
        If ClearContent = True Then
            For c = 1 To LastRowH   'if not checked, looks on current worksheet (within loop) and if any "Y" or "y" vals, clears them
                If ws.Range("H" & c).Value = "Y" Or ws.Range("H" & c).Value = "y" Then
                    ws.Range("H" & c).ClearContents
                End If
            Next
        End If
    ...

cbtrue is just a variable to see if the checkbox exists, hence if it does it will then go to the if statement, at which point it will determine whether that checkbox is ticked or not, depending on this it saves the ClearContent variable (which I use later on in the code).

The problem is when it comes to 'Shapes("ws.Name")', ws.name is simply the name of the worksheet on each loop. So on the first round of the loop, it will be "Summary"... However, I think it is physically searching for the sheet "ws.name" which obviously doesn't exist. I have tried removing it from the quotation marks and also various other methods such as 'Checkboxes("ws.Name")' but it seems they all have the same problem.

I am posting to see if someone could offer me another method, or perhaps show me where I have gone wrong, as I think I am not fully understanding the syntax.

Any Help is appreciated. Thanks in advance :)

UPDATE

I have changed the code with the help of @Xabier (I have added in an extra if statement to ensure that the CheckBox has the same name as the worksheet. I am now getting no errors, but when i run it, it isn't clearing the contents of any of the cells i requested. I cannot seem to spot why it is doing this, If anyone could spot it and let me know, that'd be great. Thanks :)

Answer 1

There are two problems with your code:

Aspect 1: Name or Text?

What I think might be a problem here is understanding what the name of a checkbox is in Excel:

The text you see besides the checkbox is not the actual name of the checkbox. You can give the checkbox a name by selecting it with a right mouse click and entering the desired name in the top left box for selection name.

Use this sample code to see the names and display texts your checkboxes actually use:

For Each CheckBox In Sheets("Summary").CheckBoxes
    MsgBox "Name: " & CheckBox.Name & " Text: " & CheckBox.Text
Next CheckBox

It will produce something like this:

If I am correct, to solve your problem you have to either give the checkboxes the correct name (the names of the worksheets) or instead of comparing Name, compare using the Text property of the checkbox.

Aspect 2: Value is not true / false

The value of the checkbox is not True or False, but the checkbox value can be either xlOn or xlOff. So, don't test for True or False, but use the correct constants.

Working code using displayed Text instead of Name and using correct value constants

Dim ws As Worksheet
Dim ClearContent  As Boolean
Dim CheckBox As CheckBox

For Each ws In ActiveWorkbook.Worksheets

    ' Reset ClearContent for each Sheet
    ClearContent = False

    For Each CheckBox In Sheets("Summary").CheckBoxes
        ' Depending on your requests, compare either with Name or with Text
        If CheckBox.Text = ws.Name Then
            ' Use xlOff constant instead of False
            If CheckBox.Value = xlOff Then
                ClearContent = True
            End If

            ' We can exit the foreach loop when we found the correct checkbox
            Exit For
        End If
    Next CheckBox

    If ClearContent Then
        MsgBox "Going to clear " & ws.Name
    End If
Next ws