Worksheet.Visible = xlSheetVeryHidden

[shanep]

Hi
I have a workbook with 2 worksheets "AppID" and "Server - Detail".

I have the following two subroutines that I want to use to show and hide these worksheets.

The problem I'm encountering is when I run the "hideSheets" code it stops when trying to hide the second of the two sheets (whichever order I put them in) with an error:

"RTE:'1004':
Unable to set the visible property of the Worksheet class"

It appears that the code is actually hiding the workbook when it hides the first sheet, and therefore when it tries to hide the second sheet it cannot find it to hide.....

Sub hideSheets()
ThisWorkbook.Worksheets("AppID").Visible = xlSheetVeryHidden
ThisWorkbook.Worksheets("Server - Detail").Visible = xlSheetVeryHidden
End Sub
 
Sub showSheets()
ThisWorkbook.Worksheets("AppID").Visible = xlSheetVisibile
ThisWorkbook.Worksheets("Server - Detail").Visible = xlSheetVisible
End Sub

Any ideas...?

 

[Andrew Poulsom]

A workbook must have at least one visible worksheet.

 

[VoG]

You must always have at least one sheet visible.

 

[Richard Schollar]

Hi

You need to have at least one visible sheet in the workbook. If you want to hide the entire workbook (eg as Personal.xls is) then you need to hide the workbook (not the worksheets):

ThisWorkbook.Windows(1).Visible = False

 

[shanep]

ah, do i feel dumb....

i'll create a blank wsheet and hide the other two!

cheers guys!

 

[shanep]

ok guys, on the same subject...

I have created a temp wsheet and named it "TempSheet", then hidden the other two so all the user sees is a blank worksheet. That works fine.

However, I'm trying to make the original two visible then delete the "TempSheet" when the user closes the workbook.

I have the following code in the WorkBook.BeforeClose event:

    ThisWorkbook.Worksheets("Server - Detail").Visible = xlSheetVisible
    ThisWorkbook.Worksheets("AppID").Visible = xlSheetVisible
    ThisWorkbook.Worksheets("TempSheet").Delete
    ThisWorkbook.Close savechanges:=True

but it isn't deleting the "TempSheet" wsheet. Any thoughts?

 

[Colin Legg]

Closing the workbook in the before_close event handler is going to cause an infinite loop. It's going to close anyway, so just save it:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    Me.Worksheets("Server - Detail").Visible = xlSheetVisible
    Me.Worksheets("AppID").Visible = xlSheetVisible
 
    Application.DisplayAlerts = False
    On Error Resume Next
    Me.Worksheets("TempSheet").Delete
    On Error Goto 0
    ThisWorkbook.Save
    Application.DisplayAlerts = True
 
End Sub

Incidentally, I don't know what all this is trying to achieve (what is the big picture?), but why not just hide that temp worksheet? I assume you will be adding it again when the workbook is next opened?

 

[shanep]

thanks Colin

you're spot on, so I've just hidden it on closing and made it visible on opneing the wbook.

also removed the close from the workbook.beforeclose event.

cheers