Code to hide columns problem

[Steph]

Hi. I wrote what I thought was going to be simple code. I added
check boxes to my workbook using the control toolbox. The following
code is called by one checkbox. All I am trying to do is hide columns
A on 4 spreadsheets. When I run this I get the error Compile Error:
Invalid Outside Procedure.

Public SheetArray As Sheets

set shtarray = Sheets((DomShip, ConShip, IntShip, FinShip)) 'these
sheets are named in the properties window

Sub HideQtly2003()

Application.ScreenUpdating = False
shtarray.Columns("A").EntireColumn.Hidden = True
Application.ScreenUpdating = True

End Sub


Any ideas? Thanks.

 

[Guest]

I don't understand what this line is trying to do ..

set shtarray = Sheets((DomShip, ConShip, IntShip, FinShip))

Well, actually, I do, but it is not a valid syntax. The closest I can think of is ..

shtarray = Array("DomShip", "ConShip", "IntShip", "FinShip")

This will create an array - of the NAMES of the sheets

Then in your HideQtly2003 subroutine you can access them with something like ..

for each name in shtarra
workbook.Sheet(name)..Columns("A").EntireColumn.Hidden = Tru
nex

This is 'air code', but I'm sure it's cloaser to what you need than your formulation

BTW, the missing external routine is almost certainly Sheets() in that first line of code

Tom Laveda


----- Steph wrote: ----

Hi. I wrote what I thought was going to be simple code. I adde
check boxes to my workbook using the control toolbox. The followin
code is called by one checkbox. All I am trying to do is hide column
A on 4 spreadsheets. When I run this I get the error Compile Error
Invalid Outside Procedure

Public SheetArray As Sheet

set shtarray = Sheets((DomShip, ConShip, IntShip, FinShip)) 'thes
sheets are named in the properties windo

Sub HideQtly2003(

Application.ScreenUpdating = Fals
shtarray.Columns("A").EntireColumn.Hidden = Tru
Application.ScreenUpdating = Tru

End Su


Any ideas? Thanks

 

[ross]

Hi,
not very elegent but should do the job, stick it all in the click
event of the check box

x = ActiveSheet.Name

If CheckBox1.Value = True Then

Sheets("DomShip").Activate
Sheets("DomShip").Range("A").Select
Selection.EntireColumn.Hidden = True
Sheets("ConShip").Activate
Sheets("ConShip").Range("A").Select
Selection.EntireColumn.Hidden = True
Sheets("IntShip").Activate
Sheets("IntShip").Range("A").Select
Selection.EntireColumn.Hidden = True
Sheets("FinShip").Activate
Sheets("FinShip").Range("A").Select
Selection.EntireColumn.Hidden = True


Else

Sheets("DomShip").Activate
Sheets("DomShip").Range("A").Select
Selection.EntireColumn.Hidden = False
Sheets("ConShip").Activate
Sheets("ConShip").Range("A").Select
Selection.EntireColumn.Hidden = False
Sheets("IntShip").Activate
Sheets("IntShip").Range("A").Select
Selection.EntireColumn.Hidden = False
Sheets("FinShip").Activate
Sheets("FinShip").Range("A").Select
Selection.EntireColumn.Hidden = False

End If

Sheets(x).Activate
Sheets(x).Range("F1").Select


good luck
ross