Keyboard Shortcuts - Protect Worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Maurice - 27 Jun 2006 11:37 GMT

Is there a keyboard shortcut for "protect worksheet" function. I have a
workbook with 45+ sheets which needs to be individually protected, as global
protection is not possible. It become a bit laborious to move up and down
with your cursor, between the protect worksheet icon in the toolbar, and
selecting successive sheets down the bottom. I am hoping there is a faster
way to go about it. Can you help out with some ideas please ?

Reply to this Message

Paul B - 27 Jun 2006 11:44 GMT

Maurice, you could use a macro and do them all at once

Sub Protect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect password:="123"
Next ws
End Sub

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> Is there a keyboard shortcut for "protect worksheet" function. I have a
> workbook with 45+ sheets which needs to be individually protected, as global
> protection is not possible. It become a bit laborious to move up and down
> with your cursor, between the protect worksheet icon in the toolbar, and
> selecting successive sheets down the bottom. I am hoping there is a faster
> way to go about it. Can you help out with some ideas please ?

Reply to this Message

Maurice - 28 Jun 2006 10:15 GMT

Hi Paul,

Thanks for the suggestion. It looks interesting however, how do I actually
go about setting up / running such a macro ? Yes, I'm not familiar with
macros at all. A short "work instruction" in this specific instance will be
very helpful to me !

Kind regards

> Maurice, you could use a macro and do them all at once
>
[quoted text clipped - 12 lines]
> > selecting successive sheets down the bottom. I am hoping there is a faster
> > way to go about it. Can you help out with some ideas please ?

Reply to this Message

Paul B - 28 Jun 2006 11:50 GMT

Maurica, missed this was in newusers or I would have posted the instructions
with it, try this,

To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is at the top-left of the spreadsheet this will
open the VBA editor, in Project Explorer click on your workbook name, if you
don't see it press CTRL + r to open the Project Explorer, then go to insert,
module, and paste the code in the window that opens on the right hand side,
press Alt and Q to close this window and go back to your workbook and press
alt and F8, this will bring up a box to pick the Macro from, click on the
Macro name to run it. If you are using excel 2000 or newer you may have to
change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> Hi Paul,
>
[quoted text clipped - 21 lines]
> > > selecting successive sheets down the bottom. I am hoping there is a faster
> > > way to go about it. Can you help out with some ideas please ?

Reply to this Message

Maurice - 13 Jul 2006 13:03 GMT

Hi Paul,

Finally got around to putting your advice to the test. It worked like a
charm of course. Thanks very much, as it saves me a lot of effort (and
aggravation).
PS. I realized (fortunately the first time around), that after setting up
and running the macro, one should actually delete it afterwards. Otherwise
anyone can access the code again later, and gain access to the applicable
password within the script. Not so ?

Best regards
Maurice

> Maurica, missed this was in newusers or I would have posted the instructions
> with it, try this,
[quoted text clipped - 41 lines]
> faster
> > > > way to go about it. Can you help out with some ideas please ?

Reply to this Message