simultaneous protection

**Sebastian1942** · 12-18-2009, 02:31 AM

I have set up a workbook containing 15 sheets. 12 of them are named Jan to Dec. I KNOW how to protect each one, but is it possible to protect all twelve in one go ?

**davesexcel** · 12-18-2009, 09:07 AM

VBA Code is required because you cannot select all the sheets and select protect.
You can loop through the sheets and protect them.

Please Login or Register  to view this content.

**Sebastian1942** · 12-18-2009, 09:35 PM

Thank you Dave
for
1. establishing the impossibility of using a function/technique
and
2. for giving me an alternative.

It will be a challenge for me to 'deploy' the script (?),
but I will try,
and WHEN I fail,
will tell you what bungling resulted in failure.

**Sebastian1942** · 12-18-2009, 10:08 PM

I opened the first sheet of the workbook
right-clicked the sheet tab
selected 'view code'
pasted your code into the blank page that opened
replaced 'Dim' with the name of the first sheet in the workbook
replaced 'activeworkbook' with the name of the excelfile, (EXCLUDING '.xls')
Saved the subroutine
That caused the CHANGED 'Dim sh As Worksheet' to appear in red font
Clicked File menu and selected 'Close and return to MSExcel'
WHAT NOW ?
That sheet still is unprotected.

Please suffer fools kindly.

**davesexcel** · 12-18-2009, 10:19 PM

Press Alt & F11
Go to insert=>Module, paste the code there.
Press Alt & F11 again to get back to Excel
Press Alt & F8 to select a macro to run, select the macro and run it.

**Sebastian1942** · 12-19-2009, 05:10 AM

MAGIC !
Excellent
Thank you
And without ANY subsitutions !

F.Y.I.
Even though the macro was effective,
when I reopened the workbook
there was a warning message
that the sedcurity level is too high for macros to work
YET it had worked !

**teylyn** · 12-19-2009, 05:41 AM

set macro security to "medium", then you can decide on a file by file basis whether you want to run macros for the file or not!

**Sebastian1942** · 12-19-2009, 09:08 AM

teylyn
I was drawing attention to the paradoxical fact that the macro had worked, yet I got the warning to first alter secirity !

In my fumbling to find the 'scale' to enhance Dave's reputation, I accidentally clicked 'mine' and got the enigmatic statement that I had ten points - for what ? - for asking a challenging question ?

**teylyn** · 12-19-2009, 04:38 PM

Sebastian, I think the macro ran the first time because you installed the code and ran it before you closed the workbook. Then when you opened it again, macro security checke in.

Everbody starts out with 10 points. It's the forum default and makes you feel a lot better than 0 points

**davesexcel** · 12-22-2009, 10:41 AM

Your thread has been moved and renamed to here,
http://www.excelforum.com/excel-gene...s-enabled.html

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.