hide all sheets when macro is disabled

continued from: http://www.excelforum.com/excel-prog...-interval.html

Originally Posted by jewelsharma

Option Explicit forces explicit declaration of all variables in the code. That is why i had to declare ws as worksheet using the Dim statement before using it in the code. It is recommended to have it - however for this code it wouldn't have mattered.

I'm not sure I exactly follow what you imply here. But yes, you can hide all the sheets at first and then "unhide" at the start of the macro. Let's say you have a "Start" sheet.. then:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
     If ws.name <> "Start" Then
     ws.Visible = True
     If Left(ws.Name, 2) < Format(Date, "mm") Or (Left(ws.Name, 2) = Format(Date, "mm") And Right(ws.Name, 2) < Format(Date, "dd")) Then
               ws.Cells.Locked = True
               ws.Protect Password:="cybrkada"
          Else
               ws.Unprotect Password:="cybrkada"
          End If
     End If
     Next
End Sub

Please do mark this thread as "Solved". If you have any follow-up queries, kindly raise them via separate thread. You may provide a link to this thread within the new thread to connect them. This way you are likely to receive faster responses from the forum. Cheers!

Please help! The workbook is used by other people too. I cannot just tell them to hide the sheets, it defeats the purpose. What I want is, when macro is disabled, they will only see the START sheet, otherwise, all sheets will be visible. To add to this, a new sheet is added everyday. Sheets name are calendar dates in mmdd format

Thanks for the link! I have copied the codes and successfully made it to work except for the cells being unlocked after all the sheets become visible. How do I fix it? Thanks again!

Option Explicit

Private Const C_SHEETSTATE_NAME = "SheetState"
Private Const C_INTRO_SHEETNAME = "Introduction"    '<<<< CHANGE
Private Const C_WORKBOOK_PASSWORD = "abc"           '<<<< CHANGE

Sub SaveStateAndHide()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SaveStateAndHide
' This is called from Workbook_BeforeClose.
' This procedure saves the Visible propreties of all worksheets
' in the workbook. This will run only if macros are enabled. It
' saves the Visible properties as a colon-delimited string, each
' element of which is the Visible property of a sheet. In the
' property string, C_INTRO_SHEETNAME is set to xlSheetVeryHidden
' so that if the workbook is opened with macros enabled, that
' sheet will not be visible. If macros are not enabled, only
' that sheet will be visible.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim S As String
    Dim ws As Object
    Dim N As Long
    ''''''''''''''''''''''''''''''''''''''''''''
    ' Protection settings. We must be
    ' able to unprotect the workbook in
    ' order to modify the sheet visibility
    ' properties. We will restore the
    ' protection at the end of this procedure.
    ''''''''''''''''''''''''''''''''''''''''''''
    Dim HasProtectWindows As Boolean
    Dim HasProtectStructure As Boolean
    
    '''''''''''''''''''''''''''''''''''''''''''''''
    ' Save the workbook's protection settings and
    ' attempt to unprotect the workbook.
    '''''''''''''''''''''''''''''''''''''''''''''''
    HasProtectWindows = ThisWorkbook.ProtectWindows
    HasProtectStructure = ThisWorkbook.ProtectStructure
    
    ThisWorkbook.Unprotect Password:=C_WORKBOOK_PASSWORD
    
    '''''''''''''''''''''''''''''''''''''''''''''''
    ' Make the introduction sheet visible
    '''''''''''''''''''''''''''''''''''''''''''''''
    ThisWorkbook.Worksheets(C_INTRO_SHEETNAME).Visible = xlSheetVisible
    '''''''''''''''''''''''''''''''''''''''''''''''
    ' Delete the Name. Ignore error if it doesn't
    ' exist.
    On Error Resume Next
    '''''''''''''''''''''''''''''''''''''''''''''''
    ThisWorkbook.Names(C_SHEETSTATE_NAME).Delete
    Err.Clear
    On Error GoTo 0
    For Each ws In ThisWorkbook.Sheets
        '''''''''''''''''''''''''''''''''''''''''''''''
        ' Create a string of the sheet visibility
        ' properties, separated by ':' characters.
        ' Do not put a ':' after the last sheet. Always
        ' set the visible property of the Introduction
        ' sheet to xlSheetVeryHidden. Don't put a ':'
        ' after the last sheet visible property.
        '''''''''''''''''''''''''''''''''''''''''''''''
        S = S & IIf(StrComp(ws.Name, C_INTRO_SHEETNAME, vbTextCompare) = 0, _
            CStr(xlSheetVeryHidden), CStr(ws.Visible)) & _
            IIf(ws.Index = ThisWorkbook.Sheets.Count, "", ":")
        '''''''''''''''''''''''''''''''''''''''''''''''
        ' If WS is the intro sheet, make it visible,
        ' otherwise make it VeryHidden. This sets all
        ' sheets except C_INTRO_SHEETNAME to very
        ' hidden.
        ''''''''''''''''''''''''''''''''''''''''''''''''
        If StrComp(ws.Name, C_INTRO_SHEETNAME, vbTextCompare) = 0 Then
            ws.Visible = xlSheetVisible
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    Next ws
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Save the property string in a defined name.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Set the workbook protection back to what it was.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ThisWorkbook.Protect C_WORKBOOK_PASSWORD, _
        structure:=HasProtectStructure, Windows:=HasProtectWindows
    
End Sub


Sub UnHideSheets()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' UnHideSheets
' This is called by Workbook_Open to hide the introduction sheet
' and set all the other worksheets to their visible state that
' was stored when the workbook was last closed. The introduction
' sheet is set to xlSheetVeryHidden. This maro is executed only
' is macros are enabled. If the workbook is opened without
' macros enabled, only the introduction sheet will be visible.
' If an error occurs, make the intro sheet visible and get out.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Dim S As String
    Dim N As Long
    Dim VisibleArr As Variant
    Dim HasProtectWindows As Boolean
    Dim HasProtectStructure As Boolean
    
    '''''''''''''''''''''''''''''''''''''''''''''''
    ' Save the workbook's protection settings and
    ' attempt to unprotect the workbook.
    '''''''''''''''''''''''''''''''''''''''''''''''
    HasProtectWindows = ThisWorkbook.ProtectWindows
    HasProtectStructure = ThisWorkbook.ProtectStructure
    
    ThisWorkbook.Unprotect Password:=C_WORKBOOK_PASSWORD
    
    On Error GoTo ErrHandler:
    Err.Clear
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Get the defined name that contains the sheet visible
    ' properties and clean up the string.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo
    S = Mid(S, 4, Len(S) - 4)
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Set VisibleArr to an array of the visible properties,
    ' one element per worksheet.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If InStr(1, S, ":", vbBinaryCompare) = 0 Then
        VisibleArr = Array(S)
    Else
        VisibleArr = Split(S, ":")
    End If
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Loop through the array and set the Visible propety
    ' for each sheet. If we're processing the C_INTRO_SHEETNAME
    ' sheet, make it Visible (since it may be the only
    ' visbile sheet). We'll hide it later after the
    ' loop.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    For N = LBound(VisibleArr) To UBound(VisibleArr)
        If StrComp(ThisWorkbook.Sheets(N - LBound(VisibleArr) + 1).Name, C_INTRO_SHEETNAME) = 0 Then
            ThisWorkbook.Sheets(C_INTRO_SHEETNAME).Visible = xlSheetVisible
        Else
            ThisWorkbook.Sheets(N - LBound(VisibleArr) + 1).Visible = CLng(VisibleArr(N))
        End If
    Next N
    
    ''''''''''''''''''''''''''''''''
    ' Hide the INTRO sheet.
    ''''''''''''''''''''''''''''''''
    ThisWorkbook.Sheets(C_INTRO_SHEETNAME).Visible = xlSheetVeryHidden

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Set the workbook protection back to what it was.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ThisWorkbook.Protect Password:=C_WORKBOOK_PASSWORD, _
        structure:=HasProtectStructure, Windows:=HasProtectWindows
    
    Exit Sub
    
ErrHandler:
    ThisWorkbook.Worksheets(C_INTRO_SHEETNAME).Visible = xlSheetVisible
    
End Sub

Sorry i was too fast i forgot to protect the sheet first, my bad! :-(