Solved: Hide Sheet When Closing the File

[GoKats78]

I have a sheet I have being hidden upon closing the file. That works fine.. here's the code for that:

 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
 Sheets("Open Items").Visible = True
    Sheets("Open Items").Select
     Range("A1").Select
 Sheets("Open Items").Select
    ActiveWindow.SelectedSheets.Visible = False
    Range("D11").Select
 
End Sub

I had to, in order for it to work properly (at least with my limited skills), unhide the sheet first, then hide it...

But, here is the problem. When more than one workbook is open and the user closes Excel entirely, rather than closes just the file in which they are working a "Run Time Error 1004 Select method of Worksheet Class Failed" appears...

When hitting the debug the "Sheets("Open Items").Select" line is highlighted...

What to do...What to do...

[GoKats78]

I changed the code to fire when opening rather than at closing...it looks like that will do it.

[markmrw]

here is what i use

[vba]
Private Sub Auto_Close()
Application.ScreenUpdating = False
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
WS.Visible = xlSheetVisible
Next WS

Sheets("Sheet1").Visible = xlSheetHidden

Application.ScreenUpdating = True
End Sub


[/vba]

Hope this helps

Mark

[Hellyworld]

Hi Mark,

Your code helped me too but now I need to make a modification and seems it is not working as I am intending.

I have 43 sheets and I need to set them on Very Hidden and only one sheet to be visible "Dashboard". This must be done on Auto_Close because on Woorkbook_Open I have another code that will open the sheets based on Application.UserName.

[vba]
Private Sub Auto_Close()
Application.ScreenUpdating = False
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
WS.Visible = xlSheetVisible
Next WS

Sheets("Sheet1").Visible = xlSheetHidden

Application.ScreenUpdating = True
End Sub


[/vba]

If you need more details please let me know.

Many thanks!

[rlv]

Not tested.

Private Sub Auto_Close()
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        Select Case WS.Name
        Case "Dashboard"
            WS.Visible = xlSheetVisible
        Case Else
            WS.Visible = xlSheetVeryHidden
        End Select
    Next WS
End Sub

[SamT]

I have a sheet I have being hidden upon closing the file. That works fine.. here's the code for that:

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
     
    Sheets("Open Items").Visible = True 
    Sheets("Open Items").Select 
    Range("A1").Select 
    Sheets("Open Items").Select 
    ActiveWindow.SelectedSheets.Visible = False 
    Range("D11").Select 
     
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Sheets("Open Items").Visible = xlSheetHidden
End Sub

[Hellyworld]

Thank you rvl!
Your code is working as I intended.

Not tested.

Private Sub Auto_Close()
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        Select Case WS.Name
        Case "Dashboard"
            WS.Visible = xlSheetVisible
        Case Else
            WS.Visible = xlSheetVeryHidden
        End Select
    Next WS
End Sub

I thank you all for the support and posts.