xlSheetVeryHidden vs xlSheetVisible

[Guest]

Been scratching my head for a few hours and am ready to call in the "A" team...

My project was opening fine and having no problems. I changed a macro
assignment on a button and now am getting an error when I open the project:

"Unable to set the Visible property of the Worksheet class"

My XL2003 project consists of a workbook with 9 worksheets.
I have (had?) the project set up so that when the workbook opens there is an
"entrance" sheet that explains to my user how to use the program. All of the
other sheets are hidden. My user clicks on a button and this opens up the
quote form...

But now my project is opening with the error and the sheet that is open is
the quote form...

When I go to the workbook module (where I am setting the visible sheets, etc
in my Workbook_Open event and try and run this sub I get this error message:

"Application defined or Object defined error"

But I can't find what I've gotten goofed up...

In a seperate module I have:

Public Const PWORD_Workbook As String = "password"


And in my ThisWorkbook module I have:

Private Sub Workbook_Open()
'
' Make all sheets very hidden except Entrance sheet

Application.EnableEvents = False
ThisWorkbook.Unprotect (PWORD_Workbook)

ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden
' Make Entrance Sheet visible and active
ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible
Worksheets("Entrance").Activate

ThisWorkbook.Protect (PWORD_Workbook)
Application.EnableEvents = True

End Sub

Can you see what I have goofed up and help me ungoof it?

TIA,

Steve

 

[Guest]

did you protect the workbook through the tools menu? If so, this could be
the problem.

 

[Bob Phillips]

You are probably trying to hide them all before making another visible, one
must be visible at all times

Private Sub Workbook_Open()
'
' Make all sheets very hidden except Entrance sheet

Application.EnableEvents = False
ThisWorkbook.Unprotect (PWORD_Workbook)

' Make Entrance Sheet visible and active
ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible

ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden

Worksheets("Entrance").Activate

ThisWorkbook.Protect (PWORD_Workbook)
Application.EnableEvents = True

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Die_Another_Day]

Not sure if this helps any but I can significantly shorten the code for
you.

Private Sub Workbook_Open()
' Make all sheets very hidden except Entrance sheet
Dim ws As Worksheet
Application.EnableEvents = False
ThisWorkbook.Unprotect (PWORD_Workbook)

For Each ws in ThisWorkbook.Sheets
If ws.Name <> "Entrance" Then
ws.Visible = -2 'VeryHidden, 0 For Hidden
Else
ws.Visible = -1 'Visible
End If
Next
Worksheets("Entrance").Activate

ThisWorkbook.Protect (PWORD_Workbook)
Application.EnableEvents = True

End Sub

Charles Chickering

 

[Guest]

Whoops - missed your command at the beginning and end.

if you don't leave any sheets visible but the Entrance sheet, then I would
move this command to be the first action you take before you hide any sheets:
ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible

a workbook has to always have at least one sheet visible.

If that doesn't help, then what has changed since it last worked?

 

[Guest]

Hi Tom,

First, thanks for the quick reply.

I had originally protected the workbook and worksheets via the TOOLS menu
but have since not re-protected using that menu.

I'm hoping to eventually figure out how to do that just via the
workbook/worksheet code (although that is still out of my reach right now)

Any other ideas?

TIA,

Steve

 

[Guest]

with the caution that Entrance can not be the last sheet.

 

[Guest]

well, you are already doing the workbook level protection and unprotect in
your code, so it shouldn't take a lot of spinup.

But see my followup post

 

[Guest]

Tom (& Charles):

Thanks so much for your help!

It was indeed the sequence that I was hiding the sheets in. I'd added the
entrance sheet at the end and while I was testing had another sheet (quote
form) still visible...

Next question:

I (naturally) like the much shortened code that Charles wrote but I don't
know how to make sure that the "Entrance" sheet is visible first (its
actually sheet(9))...

And my last question of the day will be: where can I go to learn how to
protect the formatting, formulaes, etc in the cells of individual worksheets
via the VBA code instead of using the TOOLS menu?

TIA,

Steve

 

[Die_Another_Day]

Private Sub Workbook_Open()
' Make all sheets very hidden except Entrance sheet
Dim ws As Worksheet
Application.EnableEvents = False
ThisWorkbook.Unprotect (PWORD_Workbook)

ThisWorkbook.Worksheets("Entrance").Visible = -1
For Each ws in ThisWorkbook.Sheets
If ws.Name <> "Entrance" Then
ws.Visible = -2 'VeryHidden, 0 For Hidden
End If
Next
Worksheets("Entrance").Activate


ThisWorkbook.Protect (PWORD_Workbook)
Application.EnableEvents = True


End Sub

That should fix the Visible Problem.

Charles

 

[Dave Peterson]

Just do that Entrance sheet first--outside the loop.

Private Sub Workbook_Open()
' Make all sheets very hidden except Entrance sheet
Dim ws As Object 'Worksheet
Const PWORD_Workbook As String = "hi"

Application.Screenupdating = False
ThisWorkbook.Unprotect PWORD_Workbook

worksheets("Entrance").visible = xlsheetvisible

For Each ws in ThisWorkbook.Sheets
If ws.Name <> "Entrance" Then
ws.Visible = xlsheetveryhidden 'xlsheethidden
End If
Next ws

Worksheets("Entrance").Activate

ThisWorkbook.Protect PWORD_Workbook
Application.Screenupdating = True

End Sub

I changed the numbers to VBA's constants--I think it makes it easier to read the
code later.

And I changed the .enableevents to .screenupdating. I'm guessing that Charles
wanted to hide the flickering of any sheets being hidden.

And I change the "dim ws as worksheet" to "dim ws as object". Just to match the
loop. Charles looped through Sheets--not just the worksheets.

And you can look at VBA's help for info. You may want to record a macro when
you experiment and then look at the recorded code.

 

[Guest]

Steve,
A good way to get the syntax is to turn on the macro recorder and do it
manually.

This produces ugly and inefficient code, but it does show you the properties
and methods you need to use.

You can then clean up the code and lookup the properties and methods in VBA
help.