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


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




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.





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






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.