Before Close workbook?

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

I have protected sheet and workbook with password.
I have this code and is working ok. Does anyone have some suggestions to
this code, as I am beginner?

Private Sub Workbook_BeforeClose(cancel As Boolean)
Dim L As Long
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect (5)
Me.Worksheets(1).Visible = True
For L = 2 To Me.Worksheets.Count
Me.Worksheets(L).Visible = xlVeryHidden
Next

ActiveWorkbook.Protect Password:="5", Structure:=True, Windows:=False
ActiveWorkbook.Save
End Sub

similar is for Open
 
Your code looks fine to me. And if it is working, it must be fine. Don't
think you can do it any easier or faster. Only one suggestion and it is more
an issue of developing coding practices that plan for all possible problems
than any actual anticipated problem with your code:

When referring to a collection (where there are multiple objects, like the
WorkSheets(), etc) and you need to refer to a particular one repeatedly it is
better to refer to it by name than by number. The numbering can change if
there are any deletions/insertions. So instead of the For... Next loop, I
think I would have done it like this:

Dim ThisWorksheet as Worksheet

For each ThisWorksheet in ThisWorkbook.Worksheets
If ThisWorksheet.Name<>"FirstSheet" Then ThisWorksheet.Visible =
xlVeryHidden
Next ThisWorksheet
 
Back
Top