CheckBoxes: How to reset
 

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

CheckBoxes: How to reset
 

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
|
|

CheckBoxes: How to reset
 

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

Rob wrote:

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

--

Dave Peterson

CheckBoxes: How to reset
 

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

"Jim Rech" wrote in message
...
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
|
|

CheckBoxes: How to reset
 

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



"Dave Peterson" wrote in message
...
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

Rob wrote:

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

--

Dave Peterson

CheckBoxes: How to reset
 

#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).


Rob wrote:

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

"Dave Peterson" wrote in message
...
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

Rob wrote:

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

--

Dave Peterson

--

Dave Peterson

CheckBoxes: How to reset
 

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

Dave Peterson wrote:

#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).

Rob wrote:

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

"Dave Peterson" wrote in message
...
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

Rob wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

CheckBoxes: How to reset
 

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

"Dave Peterson" wrote in message
...
#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).


Rob wrote:

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

"Dave Peterson" wrote in message
...
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

Rob wrote:

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

--

Dave Peterson

--

Dave Peterson

CheckBoxes: How to reset
 

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).

Rob wrote:

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

"Dave Peterson" wrote in message
...
#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).


Rob wrote:

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

"Dave Peterson" wrote in message
...
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

Rob wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

CheckBoxes: How to reset
 

Yes, you're right! I simply changed ("sheet1") to ("sheet2").....dummy me!!

Rob

"Dave Peterson" wrote in message
...
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).

Rob wrote:

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

"Dave Peterson" wrote in message
...
#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).


Rob wrote:

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

"Dave Peterson" wrote in message
...
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

Rob wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson