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

Well, we don't see in your code where ws comes from. Where and how is ws initialized? — NineBerry
– NineBerry, Commented Sep 5, 2018 at 11:50
I have updated the code, so you can see more easily what I have done/ what I am trying to do. I have tried changing the Shapes(ws.Name) to CheckBoxes(ws.Name)... but i still get an error message: "Unable to get the checkboxes property of the worksheet class" — George
– George, Commented Sep 5, 2018 at 12:17
I have again updated the code, I am pretty convinced there is nothing wrong with the loops etc. The only thing I can think is that the following doesn't work for form control checkboxes (only for ActiveX). CheckBox.name = ws.name Then If CheckBox.Value = False Then ClearContent = True — George
– George, Commented Sep 7, 2018 at 10:53

NineBerry · Accepted Answer · 2018-09-07 14:33:32Z

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

matley · Accepted Answer · 2018-09-05 11:22:48Z

1

Your code definitely treat "ws.name" as literal string because it is enclosed in double quotes. You can directly set ClearContent variable to true or false upon clicking the Checkbox. This way you don't have to test its value.

answered Sep 5, 2018 at 11:22

matley

996 bronze badges

5 Comments

George Over a year ago

How would I go about doing this?

matley Over a year ago

code Private Sub CheckBox1_Click() ClearContent = IIf(CheckBox1.Value, True, False) End Sub

Brian M Stafford Over a year ago

@matley I would recommend modifying your answer to include the code contained in your comment.

George Over a year ago

The problem is with that, I have lots of checkboxes and even if I wanted to code manually for each one, I have a system in place where someone can add a new sheet and it automatically generates a new checkbox with the caption/name of the new worksheet, so i guess I would need to write a new bit of code to loop accross all of the checkboxes to change their properties?

matley Over a year ago

Try ActiveWorkbook.Sheets("Summary").Shapes(ws.name).OLEFormat.Object.Value instead of ActiveWorkbook.Sheets("Summary").CheckBoxes(ws.name).Value

Xabier · Accepted Answer · 2018-09-05 12:45:36Z

0

The following code below will show you how to loop through ActiveX checkboxes on your worksheet and verify whether they are checked or unchecked:

Sub Test()
    Dim obj As OLEObject

    For Each obj In Sheets("Summary").OLEObjects
    'loop through ActiveX Checkboxes on Worksheet "Summary"
        If obj.progID = "Forms.CheckBox.1" Then
        'is it a Checkbox?
            If obj.Object = False Then
            'is the checkbox unchecked
                ClearContent = True
            End If
        End If
    Next obj
End Sub

UPDATE: For Form Control Checkboxes the following will do:

Sub Test2()
    Dim cb As CheckBox

    For Each cb In Sheets("Summary").CheckBoxes
        If cb.Value = False Then
            ClearContent = True
        End If
    Next cb
End Sub

edited Sep 5, 2018 at 12:45

answered Sep 5, 2018 at 11:12

Xabier

7,7251 gold badge11 silver badges21 bronze badges

3 Comments

George Over a year ago

Is there a way to replicate this for form control checkboxes?

Xabier Over a year ago

@George I've updated my answer to do the same with Form Controls, hope this helps.. .:)

NineBerry Over a year ago

Comapring Value with False will not work. One has to use the xlOff constant instead.

Collectives™ on Stack Overflow

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

3 Answers 3

Aspect 1: Name or Text?

Aspect 2: Value is not true / false

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

Comments

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Aspect 1: Name or Text?

Aspect 2: Value is not true / false

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

Comments

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related