Hide all worksheets in excel 6

[waynerenaud]

Using the code directly from vba help in excel but changing true to false
For Each sh In Sheets
sh.Visible = False
Next sh
causes runtime error 1004
I am assuming that this is because you cannot set all sheets to hidden (please someone confirm if this is the case)
What I want to do is open a workbook that opens a userform. I only want the userform to show so am trying to hide the worksheets (this is purely for asthetics) then set them back to visible = true on closing the userform.

Private Sub Workbook_Open()
For Each sh In Sheets
sh.Visible = False
Next sh
userform1.Show
End Sub

'CommandButton3 is the button used to close the userform
Private Sub CommandButton3_Click()
Unload UserForm1
For Each sh In Sheets
sh.Visible = True
Next sh
End Sub

How can I make this work. Thanks

 

[PHV]

Have you tried to hide the sheets when the form is loading ?
Private Sub Workbook_Open()
UserForm1.Show
End Sub

Private Sub UserForm_Initialize()
For Each sh In Sheets
sh.Visible = False
Next sh
End Sub

Private Sub CommandButton3_Click()
Unload UserForm1
For Each sh In Sheets
sh.Visible = True
Next sh
End Sub


Hope This Help
PH.

 

[waynerenaud]

Thanks for the quick response PHV. Tried it, still the same, it hides sheets 1 & 2 then errors on the last sheet. Is there a restriction in excel that stops you hiding all sheets ??

 

[RoyVidar]

See SkipVought's second reply in this thread thread707-719198, I'm afraid that's also my experience.

Roy-Vidar

 

[waynerenaud]

Thanks Roy-Vidar, I came to the same conclusion by using the menu option - format/sheet/hide - when you get to the last sheet excel gives an error saying excel must contain at least 1 visible sheet. Guess I'll just have to load a suitable picture as the background of the visible sheet.
Thanks again.

 

[MiggyD]

If this is purely for asthetics you may consider something like:

Get count of sheets
Sheets.hide = (all Sheets - 1)
Select all Rows (of last sheet)
Hide all Rows (of last sheet)
Run your form...Upon exit,
Unhide all Rows (of last sheet)
for user's use.
[optional: unhide the other Sheets]

--MiggyD

 

[GlennUK]

Instead of messing with all of the sheets, why not just make the window of your workbook hidden?

To make it hidden use:
ActiveWindow.Visible = False


And to make it visible again:
Windows("Book1&quot.Visible = True

( using your book name instead of "Book1", of course ).



Cheerzs, Glenn.

 

[yoavbs]

Try doing this- First name one of your sheets (one sheet that must contain your workbook) as "Control" and then:

Private Sub Workbook_Open()

Application.ScreenUpdating = False
On Error Resume Next
For Each Sheet In ThisWorkbook.Sheets
If Sheet.Name <> &quot;Control&quot; Then Sheet.Visible = xlSheetVeryHidden
Next
Application.ScreenUpdating = True
Userform1.Show

end sub
I guess that will set the things for u...

 

[yoavbs]

Ooppps....forgot to make them &quot;Visiable&quot; for u...

Private Sub CommandButton3_Click()

Unload Me
Application.ScreenUpdating = False
For Each Worksheet In Worksheets
Worksheet.Visible = True
Next
Application.ScreenUpdating = True

End Sub

....and that's it...

 

[GlennUK]

By the way, you can save a workbook with it's Window as hidden so that the next time you open it, it is still hidden.

I've used this many times in the past when I just want the user to see a userform displayed by a macro without the workbook containing data sheets and modules being displayed.


Glenn.

 

[xlbo]

Or for the big &quot;What da **** ?&quot; from a user, you can use

application.visible = false


and set back to true again when you want.....I'd go for Glen's tho as hiding the app means that if something goes wrong, you'll have a ghost version of excel running...

Star to Glenn as I didn't know about windows staying hidden....although it's pretty obvious in hindsight - but that's always 20/20 so you still get a star

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

I like that - Shiny from me

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[GlennUK]

Thanks for the shiny things guys.

Glad to be of help.

Cheers, Glenn