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

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

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.

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.

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.

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

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.