Multi worksheet protection & editing ability

[uncbluegal]

Hi. I've got a large workbook that has 13 seperate pages. Each of the first
12 pages has locked, hidden and cells that can be edited. The 13th sheet is
fully locked and has some partial cells selected as hidden. This is a shared
workbook that is sitting out on our server. I have to make updates to the
formulas as things change. I have set all the locked and hidden cells of
each page - and I am ready to protect it. The locked cells are all formulas
embedded into the sheets. I want users to see the formulas and click on the
cell so they can see how the total is calculated from the numbers they are
entering in the unprotected cells.

I've ready through the posts and saw where the Macro command can be used to
protect all sheets at once. Since this is a shared document and I need to
allow some fields to be modified (unlocked cells) - some not modified
(locked) - some to not show (hidden) - on each page. In the past I would
lock and hide cells on each individual page - then go "protect sheet" and
select what things I wanted people who could access the file to have
authority to do without messing up the integrity of the document. Time
consuming when there are 55 of these 13 spreadsheet files out there needing
updating on every page.

My question is this - If I use the Protect_Sheet Macro in order to password
protect all sheets at one - how do I establish what authority I want users to
have within each page? Such as select locked cells, select unlocked cells,
etc. They keep messing up the formulas and it takes hours to figure out
where the error is.

I've never set up a Macro before so if you could advise on this as it
pertains to password protection - it would be so helpful as I've spent hours
digging through help questions and I'm not make much progress.

Thank you in advance for your help!!

 

[JLatham]

Generally once you set up the individual sheets with the protection and
access that you want, just using the .Unprotect and .Protect methods without
any other parameters leaves the sheet(s) with the setup that was in place.
Be sure you test this on a copy of the workbook.

Code looks like this, of course change the password as appropriate:

Sub UnlockAllSheets()
Const thePassword = "mySecretWord"
Dim anySheet As Worksheet
For Each anySheet In ThisWorkbook.Worksheets
anySheet.Unprotect password:=thePassword
Next
End Sub

Sub LockAllSheets()
Const thePassword = "mySecretWord"
Dim anySheet As Worksheet
For Each anySheet In ThisWorkbook.Worksheets
anySheet.Protect password:=thePassword
Next
End Sub

 

[uncbluegal]

This works great - but - I'm never prompted to enter the password to run the
macro - why?

 

[Gord Dibben]

The password is provided in the code.


Gord Dibben MS Excel MVP

 

[JLatham]

As Gord said [Thanks, Gord, once again you got my back ], the password is
built into the code. To put your password into it, just change the line of
code in each of the two routines that now say
Const thePassword = "mySecretWord"
to replace mySecretWord with whatever it is you have assigned as the
password for the worksheets. Of course, if you've already run
LockAllSheets() in the real workbook, then the password has been set to
mySecretWord.

 

[uncbluegal]

I'm sorry.....I'm really not understanding this..... I need to be able to
lock down an entire work book to have all cells that I have "locked" be
unable to be edited by other users - they should only be able to edit those
cells I've left "unlocked." So I established parameters for every page and
field - then I inserted the macro as detailed in the first response I got. I
want to be able to lock password protect cells from the formulas from being
changed; therefore, I was password protecting each sheet - if a user tried to
enter info into a locked field - they were denied. Now - The code below
works but I don't want just anyone that opens the sheet to be able to just
select "run macro unlock sheet" and be able to modify my report. But that
appears to be what is happening b/c the password is part of the lock and
unlock macro code. So - how exactly do I modify this to enable the macro to
do what I want it to? So that if the user clicks on macro - in order to run
it - they must enter the password?

As Gord said [Thanks, Gord, once again you got my back ], the password is
built into the code. To put your password into it, just change the line of
code in each of the two routines that now say
Const thePassword = "mySecretWord"
to replace mySecretWord with whatever it is you have assigned as the
password for the worksheets. Of course, if you've already run
LockAllSheets() in the real workbook, then the password has been set to
mySecretWord.

This works great - but - I'm never prompted to enter the password to run the
macro - why?

 

[Gord Dibben]

You want the same password on each sheet?

You want different password on each sheet?

What you ask is very impractical. Users would be forced to enter a password
for each sheet as the code cycles through the sheets.

What if you have 30 sheets?

If you don't want users to run the macro, hide macros so's they cannot
access them.

In VBE............Tools>VBAProject Properties>Protection>Lock Project For
Viewing.

Supply a distinct pasword.

Now only you can run a macro because others don't know the macro name and
can't run from Tools>Macro>Macros.


Gord

I'm sorry.....I'm really not understanding this..... I need to be able to
lock down an entire work book to have all cells that I have "locked" be
unable to be edited by other users - they should only be able to edit those
cells I've left "unlocked." So I established parameters for every page and
field - then I inserted the macro as detailed in the first response I got. I
want to be able to lock password protect cells from the formulas from being
changed; therefore, I was password protecting each sheet - if a user tried to
enter info into a locked field - they were denied. Now - The code below
works but I don't want just anyone that opens the sheet to be able to just
select "run macro unlock sheet" and be able to modify my report. But that
appears to be what is happening b/c the password is part of the lock and
unlock macro code. So - how exactly do I modify this to enable the macro to
do what I want it to? So that if the user clicks on macro - in order to run
it - they must enter the password?

As Gord said [Thanks, Gord, once again you got my back ], the password is
built into the code. To put your password into it, just change the line of
code in each of the two routines that now say
Const thePassword = "mySecretWord"
to replace mySecretWord with whatever it is you have assigned as the
password for the worksheets. Of course, if you've already run
LockAllSheets() in the real workbook, then the password has been set to
mySecretWord.

This works great - but - I'm never prompted to enter the password to run the
macro - why?

:

Generally once you set up the individual sheets with the protection and
access that you want, just using the .Unprotect and .Protect methods without
any other parameters leaves the sheet(s) with the setup that was in place.
Be sure you test this on a copy of the workbook.

Code looks like this, of course change the password as appropriate:

Sub UnlockAllSheets()
Const thePassword = "mySecretWord"
Dim anySheet As Worksheet
For Each anySheet In ThisWorkbook.Worksheets
anySheet.Unprotect password:=thePassword
Next
End Sub

Sub LockAllSheets()
Const thePassword = "mySecretWord"
Dim anySheet As Worksheet
For Each anySheet In ThisWorkbook.Worksheets
anySheet.Protect password:=thePassword
Next
End Sub


:

Hi. I've got a large workbook that has 13 seperate pages. Each of the first
12 pages has locked, hidden and cells that can be edited. The 13th sheet is
fully locked and has some partial cells selected as hidden. This is a shared
workbook that is sitting out on our server. I have to make updates to the
formulas as things change. I have set all the locked and hidden cells of
each page - and I am ready to protect it. The locked cells are all formulas
embedded into the sheets. I want users to see the formulas and click on the
cell so they can see how the total is calculated from the numbers they are
entering in the unprotected cells.

I've ready through the posts and saw where the Macro command can be used to
protect all sheets at once. Since this is a shared document and I need to
allow some fields to be modified (unlocked cells) - some not modified
(locked) - some to not show (hidden) - on each page. In the past I would
lock and hide cells on each individual page - then go "protect sheet" and
select what things I wanted people who could access the file to have
authority to do without messing up the integrity of the document. Time
consuming when there are 55 of these 13 spreadsheet files out there needing
updating on every page.

My question is this - If I use the Protect_Sheet Macro in order to password
protect all sheets at one - how do I establish what authority I want users to
have within each page? Such as select locked cells, select unlocked cells,
etc. They keep messing up the formulas and it takes hours to figure out
where the error is.

I've never set up a Macro before so if you could advise on this as it
pertains to password protection - it would be so helpful as I've spent hours
digging through help questions and I'm not make much progress.

Thank you in advance for your help!!