Hide/protect on close

[JoshLyman]

Having some issues figuring out the right code (not super experienced). I would like, upon close, for all sheets to be protected (password 123), the workbook to be protected (password 123), and the tab called DASHBOARD to be hidden. Currently I have:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Worksheets("DASHBOARD").Visible = False
    Dim xSheet As Worksheet
    Dim xPsw As String
    xPsw = "123"
    For Each xSheet In Worksheets
        xSheet.Protect xPsw
    Next
    ActiveWorkbook.Protect Password:="123", Structure:=True, Windows:=True

End Sub

Any help would be greatly appreciated!

 

[Jeffrey Mahoney]

Hi Josh,

I changed it so the Dashboard would be very hidden. When protecting sheets it's best to specify what options you want to protect. I recorded a macro of me protecting one the sheets and then used that code as shown below. You're going to need to do the same.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Dim xSheet As Worksheet
    Dim xPsw As String
    
    xPsw = "123"
    For Each xSheet In Worksheets
      If xSheet.Name = "DASHBOARD" Then
        xSheet.Visible = xlSheetVeryHidden                            '= xlSheetHidden
      End If
      xSheet.Protect Password:=xPsw, DrawingObjects:=False, Contents:=True, _
        Scenarios:=False, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    Next
    ThisWorkbook.Protect Password:="123", Structure:=True, Windows:=True

End Sub

 

[JoshLyman]

Thanks Jeffrey! Appreciate the help