Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA clear all check boxes

Posted on 2016-09-18
14
Medium Priority
?
170 Views
Last Modified: 2016-09-18
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?
0
Comment
Question by:Euro5
  • 7
  • 5
  • 2
14 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41804022
  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
 

Author Comment

by:Euro5
ID: 41804028
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41804030
Are your checkboxes ActiveX? If so does the name start with "Checkbox"?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Euro5
ID: 41804032
The names are
Check Box 14
Check Box 72
Check Box 47
Check Box 90
Check Box 89
0
 

Author Comment

by:Euro5
ID: 41804033
Ah! They are NOT active X....  just form controls.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41804036
Dim cb As Shape
    For Each cb In ActiveSheet.Shapes
       cb.OLEFormat.Object.Value = -4146
    Next

Open in new window

0
 

Author Comment

by:Euro5
ID: 41804038
capture
Does not like it
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41804039
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41804040
It works for me. Are you sure you have form controls checkboxes?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41804043
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
 
LVL 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 2000 total points
ID: 41804044
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41804048
Wayne's code is better then mine because mine will affect all shapes, checkbox or not.
0
 

Author Closing Comment

by:Euro5
ID: 41804081
Yes it worked!! Thanks Wayne!
Thanks for the note on the clear code Martin! ;)
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41804154
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

Featured Post

Get expert help—faster!

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question