Protecting Sheet / Workbook????

[Paul]

Is there a way to get "protect sheet" quality protection
without having to protect every tab individually?
I take care of scheduling at work so I don't want anyone
to be able to change any part of the schedule but when I,
as the author, need to modify it, I hate having to
unprotect and re-protect each sheet as I go along.
"Protect Workbook" as well as "Protect and Share" workbook
doesnt' seem to give the same level of protection.
Thanks in advance for any help.
Paul.

 

[Norman Harker]

Hi Paul!

You'll need VBA for this. Here's some code to protect all sheets with
a password:

Sub ProtectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Protect Password:="not4u2see"
Next n
End Sub

And to unprotect all sheets:

Sub UnprotectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Unprotect Password:="not4u2see"
Next n
End Sub

Or if you'd like fries with that, Sir, try:

Public Sub ToggleProtect1()
' From J E McGimpsey modified by NH
Application.ScreenUpdating = False
Const PWORD As String = "not4u2see"
Dim wkSht As Worksheet
Dim statStr As String
For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
Application.ScreenUpdating = True
MsgBox Mid(statStr, 2)
End Sub


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

Hi Norman,

Thanks, your solutions look very good, unfortunately they
also look very chineese
I'm afraid I'm just a self-taught excel newby and don't
have any experience with this type of stuff.
I'm assuming its some sort of macro but I'm not sure. I've
never used one. The syntax itself is kind of self
explanatory but I don't know where to put it and how to
set it up?
Thanks again for taking the time to respond.
Paul.

 

[Norman Harker]

Hi Paul!

Print out this post so you can keep referring to it.

With your workbook book open and with the original posting I sent
active

Select all of the three subroutines from my original posting
Right click > Copy


Right click on a sheet tab
Click "View Code"
You're now in the Visual basic Editor
Select the name of your project in the top left window (the Project
Explorer)
Insert > Module
Right Click > Paste
Save

Now click the Excel icon on the left of the top toolbar

You can now execute any of these subroutines using:

Tools > Macro > Macros
Select the one you want
Run

You could also find it useful to put the three subroutines in your
Personal.xls workbook and thereby have them available to you in any
workbook that you are working on.

It's handy to have the Personal.xls in existence ready for use
immediately you want it. First establish that you don't already have
one (under the current user log-in if appropriate):



Window > Unhide [check there isn't a PERSONAL.XLS entry]

Cancel



Open a new workbook [Just so you don't damage anything you have]

Tools > Macro > Record New Macro [Up pops the Record Macro dialog]

Click dropdown arrow for "Store Macro In:"

Select "Personal Macro Workbook"

OK

Tools > Macro > Stop Recording



If you now open the Visual Basic Editor you will find that you now
have a Personal.xls file:



Alt + F11 [Opens the Visual Basic Editor]



In top left box you'll see, possibly among other listings, an entry:



Personal (PERSONAL.XLS)



Expand the tree and you'll find under Modules that you have a Module1
that contains whatever you recorded between pressing OK in the above
procedure and stopping the recording process.



Select the Personal(PERSONAL.XLS) entry

File > Save



You now have a saved Personal.xls file and you can check that it now
exists using the Window > Unhide > Cancel process.



Insert a module in this Personal Workbook and copy and paste the code
given to that workbook. And save it.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Paul]

The lights are on and now somebodies' finally home!!
Thanks again for all the help.
Paul.

-----Original Message-----
Hi Paul!

Print out this post so you can keep referring to it.

With your workbook book open and with the original posting I sent
active

Select all of the three subroutines from my original posting
Right click > Copy


Right click on a sheet tab
Click "View Code"
You're now in the Visual basic Editor
Select the name of your project in the top left window (the Project
Explorer)
Insert > Module
Right Click > Paste
Save

Now click the Excel icon on the left of the top toolbar

You can now execute any of these subroutines using:

Tools > Macro > Macros
Select the one you want
Run

You could also find it useful to put the three subroutines in your
Personal.xls workbook and thereby have them available to you in any
workbook that you are working on.

It's handy to have the Personal.xls in existence ready for use
immediately you want it. First establish that you don't already have
one (under the current user log-in if appropriate):



Window > Unhide [check there isn't a PERSONAL.XLS entry]

Cancel



Open a new workbook [Just so you don't damage anything you have]

Tools > Macro > Record New Macro [Up pops the Record Macro dialog]

Click dropdown arrow for "Store Macro In:"

Select "Personal Macro Workbook"

OK

Tools > Macro > Stop Recording



If you now open the Visual Basic Editor you will find that you now
have a Personal.xls file:



Alt + F11 [Opens the Visual Basic Editor]



In top left box you'll see, possibly among other listings, an entry:



Personal (PERSONAL.XLS)



Expand the tree and you'll find under Modules that you have a Module1
that contains whatever you recorded between pressing OK in the above
procedure and stopping the recording process.



Select the Personal(PERSONAL.XLS) entry

File > Save



You now have a saved Personal.xls file and you can check that it now
exists using the Window > Unhide > Cancel process.



Insert a module in this Personal Workbook and copy and paste the code
given to that workbook. And save it.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Norman,

Thanks, your solutions look very good, unfortunately they
also look very chineese
I'm afraid I'm just a self-taught excel newby and don't
have any experience with this type of stuff.
I'm assuming its some sort of macro but I'm not sure. I've
never used one. The syntax itself is kind of self
explanatory but I don't know where to put it and how to
set it up?
Thanks again for taking the time to respond.
Paul.



all
sheets with when
I,

.

 

[Norman Harker]

Hi Paul!

No problem! And never hesitate to ask for more help or why something
works.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

The lights are on and now somebodies' finally home!!
Thanks again for all the help.
Paul.

-----Original Message-----
Hi Paul!

Print out this post so you can keep referring to it.

With your workbook book open and with the original posting I sent
active

Select all of the three subroutines from my original posting
Right click > Copy


Right click on a sheet tab
Click "View Code"
You're now in the Visual basic Editor
Select the name of your project in the top left window (the Project
Explorer)
Insert > Module
Right Click > Paste
Save

Now click the Excel icon on the left of the top toolbar

You can now execute any of these subroutines using:

Tools > Macro > Macros
Select the one you want
Run

You could also find it useful to put the three subroutines in your
Personal.xls workbook and thereby have them available to you in any
workbook that you are working on.

It's handy to have the Personal.xls in existence ready for use
immediately you want it. First establish that you don't already have
one (under the current user log-in if appropriate):



Window > Unhide [check there isn't a PERSONAL.XLS entry]

Cancel



Open a new workbook [Just so you don't damage anything you have]

Tools > Macro > Record New Macro [Up pops the Record Macro dialog]

Click dropdown arrow for "Store Macro In:"

Select "Personal Macro Workbook"

OK

Tools > Macro > Stop Recording



If you now open the Visual Basic Editor you will find that you now
have a Personal.xls file:



Alt + F11 [Opens the Visual Basic Editor]



In top left box you'll see, possibly among other listings, an entry:



Personal (PERSONAL.XLS)



Expand the tree and you'll find under Modules that you have a Module1
that contains whatever you recorded between pressing OK in the above
procedure and stopping the recording process.



Select the Personal(PERSONAL.XLS) entry

File > Save



You now have a saved Personal.xls file and you can check that it now
exists using the Window > Unhide > Cancel process.



Insert a module in this Personal Workbook and copy and paste the code
given to that workbook. And save it.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Norman,

Thanks, your solutions look very good, unfortunately they
also look very chineese
I'm afraid I'm just a self-taught excel newby and don't
have any experience with this type of stuff.
I'm assuming its some sort of macro but I'm not sure. I've
never used one. The syntax itself is kind of self
explanatory but I don't know where to put it and how to
set it up?
Thanks again for taking the time to respond.
Paul.




-----Original Message-----
Hi Paul!

You'll need VBA for this. Here's some code to protect all
sheets with
a password:

Sub ProtectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Protect Password:="not4u2see"
Next n
End Sub

And to unprotect all sheets:

Sub UnprotectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Unprotect Password:="not4u2see"
Next n
End Sub

Or if you'd like fries with that, Sir, try:

Public Sub ToggleProtect1()
' From J E McGimpsey modified by NH
Application.ScreenUpdating = False
Const PWORD As String = "not4u2see"
Dim wkSht As Worksheet
Dim statStr As String
For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
Application.ScreenUpdating = True
MsgBox Mid(statStr, 2)
End Sub


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
message
Is there a way to get "protect sheet" quality protection
without having to protect every tab individually?
I take care of scheduling at work so I don't want anyone
to be able to change any part of the schedule but when
I,
as the author, need to modify it, I hate having to
unprotect and re-protect each sheet as I go along.
"Protect Workbook" as well as "Protect and Share"
workbook
doesnt' seem to give the same level of protection.
Thanks in advance for any help.
Paul.


.

.