Referencing set columns on multiple worksheets
 

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.

Referencing set columns on multiple worksheets
 

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

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

Referencing set columns on multiple worksheets
 

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