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

[Grizlore]

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

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

 

[JackDanIce]

Try:

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

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

 

[dmt32]

try it this way & see if helps:

Dave

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

 

[Grizlore]

Many thanks, although IF there isnt a sheet called "Sheet1" then it hangs here still --> .Visible = xlSheetVeryHidden

ANY IDEAS?

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

 

[JackDanIce]

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

 

[Grizlore]

@dmt32

Thanks, this seems to work - THANKS

Not sure why the other two failed

Thanks both for your help !

 

[dmt32]

@dmt32

Thanks, this seems to work - THANKS

Not sure why the other two failed

Thanks both for your help !

your welcome - thanks for feedback

Dave