Interesting to see this post. I just momentarily solved a bug in my
Workbook_Open macro regarding this same process. My problem was that the
macro did not account for the fact that the workbook may not be the first
book open, i.e. I was looping to unprotect/protect sheets in Workbooks(1). I
changed it to Workbooks(Workbooks.Count) to solve my problem. (This macro is
only run once when the workbook is opened.)

This sounds about like what you are trying to do. The only difference is
you don't need to unlock cells, just unprotect the sheet.

Private Sub Workbook_Open()

Dim iSheet As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = No

For iSheet = 1 To Workbooks(Workbooks.Count).Sheets.Count
Sheets(iSheet).Unprotect
Next iSheet

(do stuff here)

For iSheet = 1 To Workbooks(Workbooks.Count).Sheets.Count
Sheets(iSheet).Protect
Next iSheet

Application.ScreenUpdating = Yes
Application.Calculation = xlCalculationAutomatic

End Sub

P.S. I always use:

Global Const Yes As Boolean = True
Global Const No As Boolean = False


"RAP" wrote:

Hello,
I have an Excel file with many pages. I need to hide all but about 3 pages
for my "users." Right now, many of my macros will not run when the pages are
hidden and/or protected. If that's the way Excel works, so be it. No
problem.
My question is in regards to a strategy to give the appearance I'm seeking,
but stay within the confines of Excel functionality.

What scripting would give me the following ability:
1. A user opens the file. An auto macro runs, locking cells and hiding
pages I want hidden, so only the pages I want him/her to see are visible. I
don't want the user to be able to get into the pages and accidentaly change
formulas, etc... I do not have a "confidential information" situation, just
for protection of the forumlas and macros and to keep the file "clean &
uncomplicated"-looking for the user.
2. When a button is clicked to start a macro, have the macro first do the
following:
a. ScreenUpdating = false
b. unhide sheets (in order for macros to run: just necessary sheets or all
sheets)
c. unlock any cells needed to run the macro (may not need this if sheets
can be re-hidden before macro finishes)
d. perform macro's function
e. lock cells back
f. hide sheets
g. ScreenUpdating = True
h. Exit Sub

I don't know if this is a legitimate way to achieve my program design goal.
If there is another tact I need to take, please let me know.
Thank you,
Randy