Error in code to Check Forms Checkbox

[Steph]

Hello. This is a follow up question to a previously posted question.
I have the following code saved to my personal workbook:


Sub Check()
Dim wks As Worksheet, chbx As Object

For Each chbx In Worksheets("20", "21", "22", "78",
"87").CheckBoxes
chbx.Value = True
Next

End Sub

Where 20, 21, etc are the actual sheet names. When I run this, I get
a Comile Error: Wrong number of argumants or invalid property
assignment. And the Debugger is pointing to the word "Worksheets"

Any ideas what I am doing wrong? Thanks!!

-Steph

 

[Tom Ogilvy]

Sub Check()
Dim wks As Worksheet, chbx As checkbox

For Each wks In Worksheets("20", "21", "22", "78","87")
for each chbx in wks.CheckBoxes
chbx.Value = True
next
Next

End Sub

 

[Bob Phillips]

Hi Steph,

You need to work through the worksheets array, like so

Dim wks As Worksheet, chbx As Object

For Each wks In Worksheets(Array("20", "21", "22", "78", "87"))
For Each chbx In wks.CheckBoxes
chbx.Value = True
Next chbx
Next wks


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

whoops - missed that you were using the "array" directly:

Sub Check()
Dim wks As Worksheet, chbx As checkbox

For Each wks In Worksheets(Array("20", "21", "22", "78", "87"))
for each chbx in wks.CheckBoxes
chbx.Value = xlOn
next
Next

End Sub

or
Sub Check()
Dim wks As Worksheet, chbx As checkbox
Dim varr as Variant
varr = Array("20", "21", "22", "78","87")
For Each wks In Worksheets(varr)
for each chbx in wks.CheckBoxes
chbx.Value = xlOn
next
Next

End Sub