Open Event Code Bug

[Tim]

Hello,
I have a problem with the code below.

I am trying to 'very hide' all sheets except the "Enable Macro"
warning sheet upon closing my workbook.
When opening the workbook and if the user disabled macros I want them
to see only the warning sheet.
If the user enabled macros upon opening I want them the see all of the
sheets except:
Enable Macro Sheet
Other sheets I set as very hidden (see code upon open)
The sheets i set as very hidden will depend on the cell value
contained on the sheet "Data Input"
This data input sheet returns the Value TRUE or FALSE (ranges C3:C21)
for the workbook sheets.

The problem I face is that my code is wrong somewhere, I can;t even
close my workbook without a code area.

Any assistance you guru's could offer me would be greatly appreciated.

Thanks
Tim

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Enable Macro").Visible = True
Sheets("Cover").Visible = xlSheetVeryHidden
Sheets("Key Assumptions").Visible = xlSheetVeryHidden
Sheets("Notes").Visible = xlSheetVeryHidden
Sheets("FF&E Master").Visible = xlSheetVeryHidden
Sheets("Area Analisys").Visible = xlSheetVeryHidden
Sheets("FF&E Price Input").Visible = xlSheetVeryHidden
Sheets("Construction Costs").Visible = xlSheetVeryHidden
Sheets("Depreciation").Visible = xlSheetVeryHidden
Sheets("OE & Uniform").Visible = xlSheetVeryHidden
Sheets("Payroll").Visible = xlSheetVeryHidden
Sheets("P&L year 1").Visible = xlSheetVeryHidden
Sheets("P&L year 1-5").Visible = xlSheetVeryHidden
Sheets("Breakeven").Visible = xlSheetVeryHidden
Sheets("Cashflow").Visible = xlSheetVeryHidden
Sheets("Data Sensitization").Visible = xlSheetVeryHidden
Sheets("Executive Summary").Visible = xlSheetVeryHidden
Sheets("Data Input").Visible = xlSheetVeryHidden
Sheets("Calculations").Visible = xlSheetVeryHidden
End Sub

Private Sub Workbook_Open()
Sheets("Enable Macro").Visible = Range("Data Input C3")
Sheets("Cover").Visible = Range("Data Input C4")
Sheets("Key Assumptions").Visible = Range("Data Input C5")
Sheets("Notes").Visible = Range("Data Input C6")
Sheets("FF&E Master").Visible = Range("Data Input C7")
Sheets("Area Analisys").Visible = Range("Data Input C8")
Sheets("FF&E Price Input").Visible = Range("Data Input C9")
Sheets("Construction Costs").Visible = Range("Data Input C10")
Sheets("Depreciation").Visible = Range("Data Input C11")
Sheets("OE & Uniform").Visible = Range("Data Input C12")
Sheets("Payroll").Visible = Range("Data Input C13")
Sheets("P&L year 1").Visible = Range("Data Input C14")
Sheets("P&L year 1-5").Visible = Range("Data Input C15")
Sheets("Breakeven").Visible = Range("Data Input C16")
Sheets("Cashflow").Visible = Range("Data Input C17")
Sheets("Data Sensitization").Visible = Range("Data Input C18")
Sheets("Executive Summary").Visible = Range("Data Input C19")
Sheets("Data Input").Visible = Range("Data Input C20")
Sheets("Calculations").Visible = Range("Data Input C21")
Splash.Show

End Sub

 

[Nigel]

Tim
The first problem is that your references to the range containing the sheet
visible values need to change to explicitly reference the cell, use the
following....

Sheets("Enable Macro").Visible = Sheets("Data Input").Range("C3").Value

Secondly - what are the values you are using on the Data Input sheet to
control the visibility?

The valid value (if you use the above method) are -1 for visible, 0 Hidden
and 2 Very Hidden

Hope this helps
Cheers
Nigel

 

[Bob Phillips]

Tim,

Can't see anything wrong with the code on sight, but you can simplify the
close code with

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Enable Macro" Then
sh.Visible = xlSheetVeryHidden
End If
Next sh

End Sub

Don opening do you want to makle all sheets visible, or just those where the
range valus is True? Where do you get the error, what loine of code?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tim]

Dear Bob,
Thanks for simlifying the code below. I can now close the workbook and
show only the "Enable Macro" page.

I now get the follwoing error uopn opening my workbook

Runtine error 1004
Unable to set the Visible property of the worksheet class.

From the code below the data in the sheet "Data Input" ranges D321
return the value "-1" or "2" depending on what I set them to i.e.
visible or very hidden.

FYI I have tried the following:
1. Setting the ranges to return "xlVisible" & "xl"VeryHidden"
2. Setting the ranges to return "True" & "False"
3. Unprotected the worksheet

All to no avail.
Any help would be greatly appreciated.
Thanks Tim

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Enable Macro" Then
sh.Visible = xlSheetVeryHidden
End If
Next sh
End Sub

Private Sub Workbook_Open()
Splash.Show
Sheets("Enable Macro").Visible = Sheets("Data
Input").Range("D3").Value
Sheets("Cover").Visible = Sheets("Data Input").Range("D4").Value
Sheets("Key Assumptions").Visible = Sheets("Data
Input").Range("D5").Value
Sheets("Notes").Visible = Sheets("Data Input").Range("D6").Value
Sheets("FF&E Master").Visible = Sheets("Data
Input").Range("D7").Value
Sheets("Area Analisys").Visible = Sheets("Data
Input").Range("D8").Value
Sheets("FF&E Price Input").Visible = Sheets("Data
Input").Range("D9").Value
Sheets("Construction Costs").Visible = Sheets("Data
Input").Range("D10").Value
Sheets("Depreciation").Visible = Sheets("Data
Input").Range("D11").Value
Sheets("OE & Uniform").Visible = Sheets("Data
Input").Range("D12").Value
Sheets("Payroll").Visible = Sheets("Data Input").Range("D13").Value
Sheets("P&L year 1").Visible = Sheets("Data
Input").Range("D14").Value
Sheets("P&L year 1-5").Visible = Sheets("Data
Input").Range("D15").Value
Sheets("Breakeven").Visible = Sheets("Data
Input").Range("D16").Value
Sheets("Cashflow").Visible = Sheets("Data Input").Range("D17").Value
Sheets("Data Sensitization").Visible = Sheets("Data
Input").Range("D18").Value
Sheets("Executive Summary").Visible = Sheets("Data
Input").Range("D19").Value
Sheets("Data Input").Visible = Sheets("Data
Input").Range("D20").Value
Sheets("Calculations").Visible = Sheets("Data
Input").Range("D21").Value
End Sub

 

[Bob Phillips]

Tim,

I have just tried it, albeit on fewer sheets (Enable Macro, Data Input and
Cover), and it works fine. Which line do you get the error on?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)