Referencing set columns on multiple worksheets

[Brian]

Hi everyone,

I am just starting out with VBA for the first time. Is there any way to
reference, say, columns A to C on every worksheet from Worksheet 2-4?

As an example, I want to make columns visible/hidden by way of checkboxes. I
currently do this as follows (thanks to Bob Phillips in one of the other
newsgroups):

Worksheets("Sheet2").Columns("A:A").Hidden = NOT CheckBox1.Value
Worksheets("Sheet3").Columns("A:A").Hidden = NOT CheckBox1.Value
Worksheets("Sheet4").Columns("A:A").Hidden = NOT CheckBox1.Value

Worksheets("Sheet2").Columns("B:B").Hidden = NOT CheckBox2.Value
Worksheets("Sheet3").Columns("B:B").Hidden = NOT CheckBox2.Value
Worksheets("Sheet4").Columns("B:B").Hidden = NOT CheckBox2.Value

Worksheets("Sheet2").Columns("C:C").Hidden = NOT CheckBox3.Value
Worksheets("Sheet3").Columns("C:C").Hidden = NOT CheckBox3.Value
Worksheets("Sheet4").Columns("C:C").Hidden = NOT CheckBox3.Value

Is there a way to write something more like:

Worksheets("Sheet2-4").Columns(A:A).Hidden = NOT CheckBox1.Value

I cannot seem to find the right syntax to do this. Any help would be most
appreciated.

Best regards,
Brian.

 

[Dave Peterson]

There are somethings that work with grouped sheets, but sometimes just looping
through those worksheets is easier:

dim wks as worksheet
for each wks in worksheets(array("sheet2","sheet3","sheet4"))
wks.columns("a:a").hidden = not checkbox1.value
wks.columns("b:b").hidden = not checkbox2.value
wks.columns("c:c").hidden = not checkbox3.value
next wks

You did want checkbox1 to control column A of all 3 sheets, right?

 

[Brian]

There are somethings that work with grouped sheets, but sometimes just
looping
through those worksheets is easier:

dim wks as worksheet
for each wks in worksheets(array("sheet2","sheet3","sheet4"))
wks.columns("a:a").hidden = not checkbox1.value
wks.columns("b:b").hidden = not checkbox2.value
wks.columns("c:c").hidden = not checkbox3.value
next wks

You did want checkbox1 to control column A of all 3 sheets, right?

Hi Dave,

thank you very much! Yes, your code was exactly what I was trying to achieve
in this instance.

Best regards,
Brian.