Unselect All CheckBoxes From Excel Worksheet or Workbook with VBA Macro

Shelby21

Board Regular
Joined
Nov 21, 2017
Messages
51
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I have been through several other threads asking this same question and have tried applying all the various methods , but for some reason I cannot get the checkboxes to uncheck using vba macro.

Looking at my Excel sheet below, I have a macro setup below the table to clear all worksheet data.

When I run the macro all the data is cleared, but the checkboxes will not uncheck.

click1.PNG



Here is the vba code for the checkboxes I have highlighted above

click.PNG



Below is the code I currently have which does not work for unchecking the checkboxes

This code works just fine for another sheet I am using it on. It just does not work for the checkboxes shown in the 1st image.

code.PNG
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
1,121
Office Version
  1. 365
Platform
  1. Windows
One of two macro's should work

VBA Code:
Sub jec()
 For Each cb In ActiveSheet.OLEObjects
  If cb.progID = "Forms.CheckBox.1" Then cb.Object = False
 Next
End Sub

or

VBA Code:
Sub jec2()
ActiveSheet.CheckBoxes.Value = False
End Sub
 
Solution

rollis13

Well-known Member
Joined
Jul 30, 2012
Messages
1,179
Office Version
  1. 2016
Platform
  1. Windows
Hi to all.
Have you checked that all the checkboxes names (is case sensitive) are exactly "CheckBox" ? add this line of code to your macro, launch it and check the result in the Immediate pane.
VBA Code:
'...
Dim o      As Object
For Each o In ActiveSheet.OLEObjects
    Debug.Print o.Name                        '<- added
    If InStr(1, o.Name, "CheckBox") > 0 Then o.Object.Value = False
Next o
'...
 

Shelby21

Board Regular
Joined
Nov 21, 2017
Messages
51
Office Version
  1. 365
Platform
  1. Windows
One of two macro's should work

VBA Code:
Sub jec()
 For Each cb In ActiveSheet.OLEObjects
  If cb.progID = "Forms.CheckBox.1" Then cb.Object = False
 Next
End Sub

or

VBA Code:
Sub jec2()
ActiveSheet.CheckBoxes.Value = False
End Sub
Hi JEC,

First macro worked perfectly!

Second macro did not work.

Thank you for your help!
 

Shelby21

Board Regular
Joined
Nov 21, 2017
Messages
51
Office Version
  1. 365
Platform
  1. Windows
Hi to all.
Have you checked that all the checkboxes names (is case sensitive) are exactly "CheckBox" ? add this line of code to your macro, launch it and check the result in the Immediate pane.
VBA Code:
'...
Dim o      As Object
For Each o In ActiveSheet.OLEObjects
    Debug.Print o.Name                        '<- added
    If InStr(1, o.Name, "CheckBox") > 0 Then o.Object.Value = False
Next o
'...
Hi Rollis,

I tried your macro, but it did not work. JEC's first macro did the trick.

Thank you for your help!
 

rollis13

Well-known Member
Joined
Jul 30, 2012
Messages
1,179
Office Version
  1. 2016
Platform
  1. Windows
My code was not supposed to fix your macro, it was only for debugging purpose (the result was visible in the Immediate pane in vbe) so you could see why your macro wasn't working ... but maybe you only needed a 'ready meal'.
 

Forum statistics

Threads
1,186,004
Messages
5,955,264
Members
438,188
Latest member
DLJ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top