asked on

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?

Martin Liss

  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

Euro5

ASKER

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

Martin Liss

Are your checkboxes ActiveX? If so does the name start with "Checkbox"?

Euro5

ASKER

The names are
Check Box 14
Check Box 72
Check Box 47
Check Box 90
Check Box 89

Euro5

ASKER

Ah! They are NOT active X.... just form controls.

Martin Liss

Dim cb As Shape
    For Each cb In ActiveSheet.Shapes
       cb.OLEFormat.Object.Value = -4146
    Next

Open in new window

Euro5

ASKER

Does not like it

Martin Liss

BTW you can change your range-clearing code to

Worksheets("Enter data") .Range("A2:F2,A4:F4").ClearContents ' add the rest of the ranges

Martin Liss

It works for me. Are you sure you have form controls checkboxes?

Martin Liss

Run the Test macro in the attached.

If you can't figure out what's wrong then please attach a sample workbook.
28970665.xlsm

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

Wayne's code is better then mine because mine will affect all shapes, checkbox or not.

Euro5

ASKER

Yes it worked!! Thanks Wayne!
Thanks for the note on the clear code Martin! ;)

Wayne Taylor (webtubbs)

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