Open Event Code Bug
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)
"Tim" wrote in message
om...
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 D3:D21
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" wrote in message
...
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" wrote in message
om...
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
|