This code should hide all sheets apart from one [Could someone have a look plz]

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
Could someone have a look at this code and see if you can spot what the problem is please?

I intend this to on-shutdown hide all sheets apart from the one named "LOCKED"
and on-startup, hide "LOCKED" and show all the other.

It works if there is a sheet names "Sheet1", if not it doesn't


Code:
Option Explicit

Sub Auto_close()

Dim wsSheet As Worksheet
Application.ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
    If wsSheet.Name = "LOCKED" Then
     wsSheet.Visible = xlSheetVisible
    Else
wsSheet.Visible = xlSheetVeryHidden
    End If
Next wsSheet

ActiveWorkbook.Save
Application.ScreenUpdating = True

End Sub

'//////////////////////////////

Sub Auto_open()
    If ActiveWorkbook.ReadOnly = True Then GoTo CloseDown 
   Dim wsSheet As Worksheet
For Each wsSheet In ThisWorkbook.Worksheets
    If wsSheet.Name <> "LOCKED" Then
     wsSheet.Visible = xlSheetVisible
    End If
Next wsSheet

Sheets("LOCKED").Visible = xlSheetVeryHidden
  
End Sub

Any pointers would be appreciated
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Try:
Code:
Sub Auto_close()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    With ws
        .Visible = xlSheetVeryHidden
        If LCase(.Name) = "locked" Then .Visible = xlSheetVisible
    End With
Next wsSheet
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub
Code:
Sub Auto_open()
Dim ws As Worksheet
If ActiveWorkbook.ReadOnly = True Then GoTo CloseDown
For Each ws In ActiveWorkbook.Worksheets
    With ws
        .Visible = xlSheetVisible
        If LCase(.Name) = "locked" Then .Visible = xlSheetVeryHidden
    End With
Next wsSheet
  
End Sub
 
Upvote 0
try it this way & see if helps:

Dave

Code:
Sub Auto_close()
    Dim wsSheet As Worksheet
    Dim wsLocked As Worksheet
    Application.ScreenUpdating = False
    Set wsLocked = Worksheets("LOCKED")
    wsLocked.Visible = xlSheetVisible
    For Each wsSheet In ThisWorkbook.Worksheets
        If Not wsSheet.Name = wsLocked.Name Then wsSheet.Visible = xlSheetVeryHidden
    Next wsSheet
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
End Sub

Sub Auto_open()
    Dim wsSheet As Worksheet
    Dim wsLocked As Worksheet
    If ActiveWorkbook.ReadOnly = True Then GoTo CloseDown
    Set wsLocked = Worksheets("Locked")
    For Each wsSheet In ThisWorkbook.Worksheets
        wsSheet.Visible = xlSheetVisible
    Next wsSheet
    wsLocked.Visible = xlSheetVeryHidden
CloseDown:
End Sub
 
Last edited:
Upvote 0
Many thanks, although IF there isnt a sheet called "Sheet1" then it hangs here still --> .Visible = xlSheetVeryHidden

ANY IDEAS?

Rich (BB code):
Sub Auto_close()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    With ws
        .Visible = xlSheetVeryHidden
        If LCase(.Name) = "locked" Then .Visible = xlSheetVisible
    End With
Next ws 'Sheet
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub
 
Upvote 0
My code should be independent of the name entirely, my only guess is you have 1 or less worksheets (visible or otherwise)? Difficult to say without knowing the structure of your workbook
 
Upvote 0
@dmt32

Thanks, this seems to work - THANKS

Not sure why the other two failed

Thanks both for your help !
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top