Workbook Protect/Unprotect Strategy question

[Guest]

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

 

[Jim Rech]

1. A user opens the file. An auto macro runs, locking cells and hiding
If you distribute the workbook to users in a prepped condition why would you
need to do this?

The rest is fine but generally it's not necessary to unhide worksheets to do
something to them - if you do it the right way, that is, without
"selecting". Here's an example of what I mean:

Rookie code:
Application.ScreenUpdating = False
Worksheets("Whatever").Visible = True
Range("A1").Select
X = ActiveCell.Value
Worksheets("Whatever").Visible = False

Pro code:
X = Worksheets("Whatever").Range("A1").Value

--
Jim
| 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

 

[Guest]

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

 

[Guest]

Thanks for the pro method. I hate it when my rookie shows his face!

 

[DM Unseen]

Private Sub Workbook_Open()
Dim objSheet As Object

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = No

For each objSheet in Me.She­ets
objSheet.Unprotect
Next objSheet


(do stuff here)


For each objSheet in Me.She­ets
objSheet.Protect
Next objSheet

Application.ScreenUpdating = Yes
Application.Calculation = xlCalculationAutomatic

End Sub

This changed code handles objects more consistently

DM Unseen

 

[DM Unseen]

BTW I would just on Workbook_open run the Protect method with the
UserInterfaceOnly option.
This way you only need to unprotect and protect your sheets only once
per session, all other VBA code accesing your sheets can use the sheets
as if they where unprotected(see XL help)

DM Unseen

 

[Guest]

Much nicer. I'm learning.