CheckBoxes: How to reset

R

Rob

I'd like to know the VBA code to set all CheckBoxes on a worksheet to false.
I presume a loop would be required, but I can't get one to work.

Rob
 
J

Jim Rech

There are two kinds of checkboxes that can be put on a worksheet. From the
Forms toolbar and from the Control Toolbox. I imagine you went with the
Forms toolbar version so in that case:

ActiveSheet.CheckBoxes.Value = False

--
Jim
"Rob" <none> wrote in message
| I'd like to know the VBA code to set all CheckBoxes on a worksheet to
false.
| I presume a loop would be required, but I can't get one to work.
|
| Rob
|
|
 
D

Dave Peterson

And if they're from the Control Toolbox toolbar:

Option Explicit
Sub auto_open()
Dim wks As Worksheet
Dim OLEObj As OLEObject

Set wks = Worksheets("sheet1")

For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.Object.Value = False
End If
Next OLEObj
End Sub
 
R

Rob

Thanks Jim,

I should have mentioned they were from the Control Toolbox and Dave has
answered to that. Thanks anyway for your input as I'm sure I'll use that
down the track.

Rob
 
R

Rob

Thanks Dave, but I get a "Subscript out of range" message at the line Set
wks = Worksheets("sheet1").
I note that the procedure says MSForms.CheckBox. Is that right if the
CheckBoxes are from the Control Toolbox?

Also, does the Sub auto_open() mean that as soon as the sheet is opened this
procedure will run?

Rob
 
D

Dave Peterson

#1. Change Sheet1 to the name of your worksheet that has the checkboxes
or change
Set wks = Worksheets("sheet1")
to
set wks = activesheet

If you want to work against the currently activesheet.

#2. Yep. MSForms.Checkbox is correct

#3. Auto_Open() in a general module will open with the workbook opens (assuming
that the user allows macros to run).
 
D

Dave Peterson

#3. Auto_Open() in a general module will RUN with the workbook opens (assuming
that the user allows macros to run).
 
R

Rob

Thanks again Dave.

I changed "Worksheets("sheet1")" to just Sheet1 and it works fine.

This is probably a bit presumptious to ask of you, but I also posted another
problem headed Mail Merging: From Filtered data to Word. I don't know if
this is in your area of expertise, but I would really value any help I can
with this. I was also wondering if using pivot tables somehow would be a
solution to that problem.

Rob
 
D

Dave Peterson

You used what's called the Codename of that worksheet.

You could have changed the stuff within the double quotes to the name of the
worksheet that you see on that worksheet tab (when you're in excel).
Thanks again Dave.

I changed "Worksheets("sheet1")" to just Sheet1 and it works fine.

This is probably a bit presumptious to ask of you, but I also posted another
problem headed Mail Merging: From Filtered data to Word. I don't know if
this is in your area of expertise, but I would really value any help I can
with this. I was also wondering if using pivot tables somehow would be a
solution to that problem.

Rob
Click to expand...
 
Top