Select / unselect all CheckBoxes in a range EXCEL VBA macro

Question

I am creating a VBA macro to select all checkboxes in a sheet and it works fine but now i want to adjust my code to select and unselect just the checkboxes in a particular range.

Here is my code.

Sub Select_all()

Dim Cbox As CheckBox
Dim Rng As Range

Set Rng = ActiveWorkbook.Sheets("Sheet4").Range("B7, B104")

For Each Cbox In ActiveSheet.CheckBoxes

If Not Intersect(Cbox.TopLeftCell, Rng) Is Nothing Then
  If Cbox.name <> ActiveSheet.CheckBoxes("Check Box 104").name Then
    Cbox.Value = ActiveSheet.CheckBoxes("Check Box 104").Value
  End If
End If

Next Cbox

End Sub

I added

If Not Intersect(Cbox.TopLeftCell, Rng) Is Nothing

but it doesn't change anything, and when i remove this part then it selects all the checkboxes in the sheet and i need only for the Range("B7:B104")

Any suggestions please ? Thank you very much.

Why did you define the range as Range("B7, B104")? That means it is only two cells, B7 and B104, not the range of cells B7:B104. And then just under your code you put the correct definition of Range("B7:B104"), so just change to that. — tigeravatar
– tigeravatar, Commented May 16, 2017 at 14:09
@tigeravatar i did the same as you, i just pasted the code by mistake. — JuniorDev
– JuniorDev, Commented May 17, 2017 at 6:12

John Muggins · Accepted Answer · 2017-05-16 15:34:12Z

1

This works for me. But I changed the cb# to "1" because I don't have 104 of them on my test sheet. Any cb's in range B7:B104 will be changed to the value of cb1, which you can change to cb104 in your script.

Sub Link_Checkboxes_To_Cells()

    Dim cBox As CheckBox

        For Each cBox In Sheet1.CheckBoxes
            If Not Intersect(cBox.TopLeftCell, Range("B7:B104")) Is Nothing Then
                If cBox.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name Then
                    cBox.Value = ActiveSheet.CheckBoxes("Check Box 1").Value
                End If
            End If
        Next cBox

    End Sub

answered May 16, 2017 at 15:34

John Muggins

1,1981 gold badge6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JuniorDev Over a year ago

Thanks for your answer. but strange why it's not working. i tried a new workbook and it's the same. do i need to add some reference ? or some configurations ?

John Muggins · Accepted Answer · 2017-05-17 15:22:06Z

@JuniorDev

There are a few reasons why the above code doesn't work for you. I'll try to list them.

The above code works for "Form Control" checkboxes - Not ActiveX type checkboxes. You would need a different code for ActiveX checkboxes. Or you may not have a form checkbox named "Check Box 1". But I think that would give you an error. Or your other checkboxes may not be in the Range("B7:B104"). You can try changing that to Range("A1:Z10000") to insure your checkboxes are in the range.

If you are not sure what type checkboxes you have then you can run the following code to find their names and what type of control they are, either form or activex. It will print the info in your immediate window of the VB editor.

Sub CheckboxLoop()
'PARTIAL SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
Dim cb As Shape

'Loop through Form Checkboxes
  For Each cb In ActiveSheet.Shapes
    If cb.Type = msoFormControl Then
      If cb.FormControlType = xlCheckBox Then
            Debug.Print "Form Control: " & cb.Name
      End If
    End If
  Next cb

'Loop through ActiveX Checkboxes
Dim ChkBx As OLEObject
For Each ChkBx In ActiveSheet.OLEObjects
  If TypeName(ChkBx.Object) = "CheckBox" Then
        Debug.Print "ActiveX Control: " & ChkBx.Name
  End If
Next ChkBx
End Sub

Phoenix1 · Accepted Answer · 2020-08-07 14:03:27Z

0

Sorry to resurrect an old post, but I hate it when a solution has not been provided and I know the answer, which is seldom. Thank you, this helped me on a project I am currently working on.

If you're like me, I change all my Sheet Names for sake of clarity.

For Each cBox In Sheet1.CheckBoxes

This line in John Muggins code limits you to the Sheet Name, "Sheet1".
I was able to change the line to...

For Each cBox In ActiveSheet.CheckBoxes

...and it worked perfectly.

answered Aug 7, 2020 at 14:03

Phoenix1

1

Collectives™ on Stack Overflow

Select / unselect all CheckBoxes in a range EXCEL VBA macro

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related