Solved

VBA clear all check boxes

Posted on 2016-09-18
14
41 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 45

Expert Comment

by:Martin Liss
Comment Utility
  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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
Are your checkboxes ActiveX? If so does the name start with "Checkbox"?
0
 

Author Comment

by:Euro5
Comment Utility
The names are
Check Box 14
Check Box 72
Check Box 47
Check Box 90
Check Box 89
0
 

Author Comment

by:Euro5
Comment Utility
Ah! They are NOT active X....  just form controls.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
capture
Does not like it
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
It works for me. Are you sure you have form controls checkboxes?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
Comment Utility
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 45

Expert Comment

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

Author Closing Comment

by:Euro5
Comment Utility
Yes it worked!! Thanks Wayne!
Thanks for the note on the clear code Martin! ;)
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now