VBA clear all check boxes

I would like to reset checkboxes to empty.
The boxes may or may not be checked at the time.

How would I do this?

for instance, uncheck Check Box 14 via VBA?
Euro5Asked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
Iterate through the Checkboxes collection and set the value to False...
    Dim cb As CheckBox
    For Each cb In ActiveSheet.CheckBoxes
        cb.Value = False
    Next

Open in new window

0
 
Martin LissOlder than dirtCommented:
  Dim cb As Object ' Try Checkbox instead of Object. If Checkbox works change it.
    For Each cb In ActiveSheet.OLEObjects
        If InStr(1, cb.Name, "CheckBox") > 0 Then
            cb.Object.Value = False
        End If
    Next

Open in new window

0
 
Euro5Author Commented:
Nothing working...this is what I am using - I tried to change Object to Checkbox
Any other ideas?

Sub CLEARALL()
  Dim cb As Object ' Try Checkbox instead of Object. If Checkbox works change it.
    For Each cb In ActiveSheet.OLEObjects
        If InStr(1, cb.Name, "CheckBox") > 0 Then
            cb.Object.Value = False
        End If
    Next

Worksheets("Enter data").Range("A2:F2").ClearContents
Worksheets("Enter data").Range("A4:F4").ClearContents
Worksheets("Enter data").Range("A7:F7").ClearContents
Worksheets("Enter data").Range("A10:F10").ClearContents
Worksheets("Enter data").Range("H4:H10").ClearContents
Worksheets("Enter data").Range("K4:K10").ClearContents
Worksheets("Enter data").Range("M4:M10").ClearContents
Worksheets("Enter data").Range("I15").ClearContents
Worksheets("Enter data").Range("M15").ClearContents

End Sub

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Martin LissOlder than dirtCommented:
Are your checkboxes ActiveX? If so does the name start with "Checkbox"?
0
 
Euro5Author Commented:
The names are
Check Box 14
Check Box 72
Check Box 47
Check Box 90
Check Box 89
0
 
Euro5Author Commented:
Ah! They are NOT active X....  just form controls.
0
 
Martin LissOlder than dirtCommented:
Dim cb As Shape
    For Each cb In ActiveSheet.Shapes
       cb.OLEFormat.Object.Value = -4146
    Next

Open in new window

0
 
Euro5Author Commented:
capture
Does not like it
0
 
Martin LissOlder than dirtCommented:
BTW you can change your range-clearing code to

       Worksheets("Enter data") .Range("A2:F2,A4:F4").ClearContents ' add the rest of the ranges
1
 
Martin LissOlder than dirtCommented:
It works for me. Are you sure you have form controls checkboxes?
0
 
Martin LissOlder than dirtCommented:
Run the Test macro in the attached.

If you can't figure out what's wrong then please attach a sample workbook.
28970665.xlsm
0
 
Martin LissOlder than dirtCommented:
Wayne's code is better then mine because mine will affect all shapes, checkbox or not.
0
 
Euro5Author Commented:
Yes it worked!! Thanks Wayne!
Thanks for the note on the clear code Martin! ;)
0
 
Wayne Taylor (webtubbs)Commented:
Glad I could help :)

BTW, the below Forms controls can also be handled in the same manner...

    Buttons
    DropDowns
    Spinners
    ListBoxes
    OptionButtons
    GroupBoxes
    Labels
    ScrollBars

...as well as some shapes such as Ovals, Rectangles, Lines, etc.

Wayne
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.