Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and password protect only cells with formulas on all sheets in a workbook
Hello,
I would really appreciate help with the following. I have a workbook that has 50 worksheets in it. Each worksheet has predefined spaces for user input and a number of formulas running off the user input. Is there a way that I can lock only the cells that contain a formula and password protect each worksheet in the entire workbook at the same time without having to go to each individual sheet. All comments and suggestions welcome. Thanks, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and password protect only cells with formulas on all sheets i
No. You must protect each sheet individually, but you can do it in a macro.
-- Regards, Tom Ogilvy "steve" wrote: Hello, I would really appreciate help with the following. I have a workbook that has 50 worksheets in it. Each worksheet has predefined spaces for user input and a number of formulas running off the user input. Is there a way that I can lock only the cells that contain a formula and password protect each worksheet in the entire workbook at the same time without having to go to each individual sheet. All comments and suggestions welcome. Thanks, Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and password protect only cells with formulas on all sheets i
Thanks for your feedback Tom.
I have tried to record a macro to perform all the steps but the macro is not able to record the assignment of a unique password to the sheet. It will only lock the cells. Any other suggestions? Are you aware of any VB code that could do the trick? Cheers, Steve On Sat, 8 Jul 2006 16:37:01 -0700, Tom Ogilvy wrote: No. You must protect each sheet individually, but you can do it in a macro. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and password protect only cells with formulas on all sheets i
I use this for a large workbook and have a keyboard assignment for each. the
password is not unique to each sheet, it is always "fcc". However, I also password protect the code (project) with another password, so my code cannot be viewed. Sub unpro() ' Keyboard Shortcut: Ctrl+u ' Dim w As Worksheet For Each w In Worksheets w.Unprotect ("fcc") Next End Sub Sub protect() ' Keyboard Shortcut: Ctrl+p ' Dim w As Worksheet For Each w In Worksheets w.protect Password:="fcc" Next Sheet20.Unprotect ("fcc") Sheet65.Unprotect ("fcc") End Sub Sheets 20 & 65 are a hidden database and a menusheet so I never want them protected. All other sheets I had pre-set the cells property manualy for locked or unlocked because they have different layouts. Mike F "steve" wrote in message ... Thanks for your feedback Tom. I have tried to record a macro to perform all the steps but the macro is not able to record the assignment of a unique password to the sheet. It will only lock the cells. Any other suggestions? Are you aware of any VB code that could do the trick? Cheers, Steve On Sat, 8 Jul 2006 16:37:01 -0700, Tom Ogilvy wrote: No. You must protect each sheet individually, but you can do it in a macro. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and password protect only cells with formulas on all sheets i
Hi Steve
assignment of a unique password Do you want to use a different password for all 50 sheets Do I understand you correct -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... Thanks for your feedback Tom. I have tried to record a macro to perform all the steps but the macro is not able to record the assignment of a unique password to the sheet. It will only lock the cells. Any other suggestions? Are you aware of any VB code that could do the trick? Cheers, Steve On Sat, 8 Jul 2006 16:37:01 -0700, Tom Ogilvy wrote: No. You must protect each sheet individually, but you can do it in a macro. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and password protect only cells with formulas on all sheets i
Hi Ron,
No, what I meant was one unique password for all the worksheets. Sorry for the confusion. Cheers, Steve On Sun, 9 Jul 2006 15:24:59 +0200, "Ron de Bruin" wrote: Hi Steve assignment of a unique password Do you want to use a different password for all 50 sheets Do I understand you correct |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and password protect only cells with formulas on all sheets i
Hi Mike,
Your code works great! Thanks for your help, very much appreciated. Cheers, Steve On Sun, 9 Jul 2006 09:24:49 -0400, "Mike Fogleman" wrote: I use this for a large workbook and have a keyboard assignment for each. the password is not unique to each sheet, it is always "fcc". However, I also password protect the code (project) with another password, so my code cannot be viewed. Sub unpro() ' Keyboard Shortcut: Ctrl+u ' Dim w As Worksheet For Each w In Worksheets w.Unprotect ("fcc") Next End Sub Sub protect() ' Keyboard Shortcut: Ctrl+p ' Dim w As Worksheet For Each w In Worksheets w.protect Password:="fcc" Next Sheet20.Unprotect ("fcc") Sheet65.Unprotect ("fcc") End Sub Sheets 20 & 65 are a hidden database and a menusheet so I never want them protected. All other sheets I had pre-set the cells property manualy for locked or unlocked because they have different layouts. Mike F "steve" wrote in message .. . Thanks for your feedback Tom. I have tried to record a macro to perform all the steps but the macro is not able to record the assignment of a unique password to the sheet. It will only lock the cells. Any other suggestions? Are you aware of any VB code that could do the trick? Cheers, Steve On Sat, 8 Jul 2006 16:37:01 -0700, Tom Ogilvy wrote: No. You must protect each sheet individually, but you can do it in a macro. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to lock several sheets including af password? | Excel Discussion (Misc queries) | |||
password protect sheets | Excel Worksheet Functions | |||
View limited sheets in workbook based on logon/password protect | Excel Programming | |||
Lock and protect cells without protect the sheet | Excel Programming | |||
Protect Excel sheets with Password Using VBA | Excel Programming |