Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Close workbook?
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", Structu=True, Windows:=False ActiveWorkbook.Save End Sub similar is for Open |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Close workbook?
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 "Marc" wrote: 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", Structu=True, Windows:=False ActiveWorkbook.Save End Sub similar is for Open |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
close only one workbook | New Users to Excel | |||
to close a workbook via VBA | Excel Programming | |||
Close a the current workbook and load another specified workbook | Excel Programming | |||
Close a workbook | Excel Programming | |||
How can I close a workbook using VBA? | Excel Programming |