|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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 ?
|
|
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
--
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
"Maurice" <Maurice[ at ]discussions.microsoft.com> wrote in message
news:94AD80C4-ACF4-4910-8479-6BAC616AD489[ at ]microsoft.com...
[Quoted Text] > 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 ?
|
|
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
"Paul B" wrote:
[Quoted Text] > 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
>
>
> --
> 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
>
> "Maurice" <Maurice[ at ]discussions.microsoft.com> wrote in message
> news:94AD80C4-ACF4-4910-8479-6BAC616AD489[ at ]microsoft.com...
> > 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 ?
>
>
>
|
|
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
--
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
"Maurice" <Maurice[ at ]discussions.microsoft.com> wrote in message
news:847D6618-2EBB-4CB5-A7B8-E42FEC2C16F0[ at ]microsoft.com...
[Quoted Text] > 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
>
> "Paul B" wrote:
>
> > 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
> >
> >
> > --
> > 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
> >
> > "Maurice" <Maurice[ at ]discussions.microsoft.com> wrote in message
> > news:94AD80C4-ACF4-4910-8479-6BAC616AD489[ at ]microsoft.com...
> > > 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 ?
> >
> >
> >
|
|
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
"Paul B" wrote:
[Quoted Text] > 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
>
> --
> 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
>
> "Maurice" <Maurice[ at ]discussions.microsoft.com> wrote in message
> news:847D6618-2EBB-4CB5-A7B8-E42FEC2C16F0[ at ]microsoft.com...
> > 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
> >
> > "Paul B" wrote:
> >
> > > 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
> > >
> > >
> > > --
> > > 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
> > >
> > > "Maurice" <Maurice[ at ]discussions.microsoft.com> wrote in message
> > > news:94AD80C4-ACF4-4910-8479-6BAC616AD489[ at ]microsoft.com...
> > > > 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 ?
> > >
> > >
> > >
>
>
>
|
|
|