The password is provided in the code.


Gord Dibben MS Excel MVP

On Mon, 1 Jun 2009 13:33:01 -0700, uncbluegal
wrote:

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

"JLatham" wrote:

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" wrote:

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!!