Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi worksheet protection & editing ability
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi worksheet protection & editing ability
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!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi worksheet protection & editing ability
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!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi worksheet protection & editing ability
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!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi worksheet protection & editing ability
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" 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!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi worksheet protection & editing ability
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? "JLatham" wrote: 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" 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!! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi worksheet protection & editing ability
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............ToolsVBAProject PropertiesProtectionLock 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 ToolsMacroMacros. Gord On Fri, 5 Jun 2009 10:09:01 -0700, uncbluegal wrote: 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? "JLatham" wrote: 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" 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing a multi-line formula | Excel Discussion (Misc queries) | |||
How can I have a protected worksheet and have spell check ability | Excel Discussion (Misc queries) | |||
Editing in a multi-user environment | Excel Discussion (Misc queries) | |||
Publishing an excel file on Sharepoint for multi-editing | Excel Discussion (Misc queries) | |||
Protecting a Worksheet but ability to Sort | Excel Discussion (Misc queries) |