Workbook open code to do 2 things
 

Hi,

I originally had the following in ThisWorkbook

Private Sub Workbook_Open()
Dim sht As Worksheet
Const TopLeft As String = "a2"
Application.ScreenUpdating = False
For Each sht In Worksheets
Application.Goto sht.Range(TopLeft), Scroll:=True
sht.Protect Password:="*gwb", _
UserInterFaceOnly:=True
Next sht

Application.ScreenUpdating = True
Sheets("Sheet1").Select

Now I'm trying to make users enable macros by hiding sheets.

Sheets("Sheet1").Visible = True
Sheets("Warning").Visible = xlVeryHidden
Sheets("STEP 1").Visible = True

and so on for approx twenty sheets before the For each bit. I was intending
to make an array of sheets that I can make visible but I can't get both bits
of the macro working at the same time as I get a runtime error.

Is it because I also have some sheets in the workbook that are hidden
normally or because of the very hidden sheet?

Any ideas on what I need to do would be brilliant!

Cheers

Workbook open code to do 2 things
 

Try putting your Sheet visibility stuff after the For each sheet loop.

You could also use a select case within the For each sheet loop like this
Select Case sht.Name
Case "Sheet1", "Step 1"
sht.Visible=True
Case "Warning"
sht.Visible = xlVeryHidden
End Select

If most sheets are visible and stay that way you could shorten it up even
more like this (and not have to type each sheet name

Select Case sht.Name
Case "Warning"
sht.Visible = xlVeryHidden
Case Else
sht.Visible=True
End Select




--
If this helps, please remember to click yes.


"Diddy" wrote:

Hi,

I originally had the following in ThisWorkbook

Private Sub Workbook_Open()
Dim sht As Worksheet
Const TopLeft As String = "a2"
Application.ScreenUpdating = False
For Each sht In Worksheets
Application.Goto sht.Range(TopLeft), Scroll:=True
sht.Protect Password:="*gwb", _
UserInterFaceOnly:=True
Next sht

Application.ScreenUpdating = True
Sheets("Sheet1").Select

Now I'm trying to make users enable macros by hiding sheets.

Sheets("Sheet1").Visible = True
Sheets("Warning").Visible = xlVeryHidden
Sheets("STEP 1").Visible = True

and so on for approx twenty sheets before the For each bit. I was intending
to make an array of sheets that I can make visible but I can't get both bits
of the macro working at the same time as I get a runtime error.

Is it because I also have some sheets in the workbook that are hidden
normally or because of the very hidden sheet?

Any ideas on what I need to do would be brilliant!

Cheers