VBA Code

[Peter Muller]

Good evening,

I am using the following code in my save and close command but am getting errors if a sheet is already closed, or if the columns are already hidden. I tried using “If, Then” commands in the code, but am obviously not doing it correctly. Please help.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Report").Select
Columns("K:P").EntireColumn.Hidden = True
Columns("Q:X").EntireColumn.Hidden = True
Columns("AC").EntireColumn.Hidden = True

Sheets("Data").Visible = False
Sheets("Procedure").Visible = False

Sheets("Report").Protect "Password"
Sheets("Analysis").Protect " Password "
Sheets("Budget").Protect " Password "

End Sub

 

[DanteAmor]

Try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  With Sheets("Report")
    .Unprotect "Password"
    .Columns("K:P").EntireColumn.Hidden = True
    .Columns("Q:X").EntireColumn.Hidden = True
    .Columns("AC").EntireColumn.Hidden = True
  End With
  
  Sheets("Data").Visible = False
  Sheets("Procedure").Visible = False
  
  Sheets("Report").Protect "Password"
  Sheets("Analysis").Protect " Password "
  Sheets("Budget").Protect " Password "
End Sub

 

[Peter Muller]

Try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  With Sheets("Report")
    .Unprotect "Password"
    .Columns("K:P").EntireColumn.Hidden = True
    .Columns("Q:X").EntireColumn.Hidden = True
    .Columns("AC").EntireColumn.Hidden = True
  End With
 
  Sheets("Data").Visible = False
  Sheets("Procedure").Visible = False
 
  Sheets("Report").Protect "Password"
  Sheets("Analysis").Protect " Password "
  Sheets("Budget").Protect " Password "
End Sub

Awesome, thank you so much ?

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.