Referencing set columns on multiple worksheets

  • Thread starter Thread starter Brian
  • Start date Start date
B

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.
 
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?
 
Dave Peterson said:
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?
Click to expand...
Hi Dave,

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

Best regards,
Brian.
 
Back
Top