EXCEL1: Workbook Protection, Single Click

[aacod]

I want to protect a 31 Sheet excel workbook with data in cells ranging from A1:N54 on each of the 31 sheets. Each sheet tab is labelled as 1 Mar, 2 Mar, 3 Mar and so on till 31 Mar.

Condition:
Following Cells & Columns must be protected.

Protect Cell B4
Protect Cell K4, K5, K6
Protect Cell L4, L5, L6
Protect Cell K8
Protect Cell C54, H54, M54


Protect Column D, E, J, I and N

Other cells and columns left unprotected for user to select and/or input data.

Thanks.

 

[schielrn]

Is the protection going to apply to every sheet in the workbook?

 

[aacod]

yes Sir.

 

[schielrn]

ok then maybe try:

Sub protectdata()
For Each ws In Worksheets
    ws.Cells.Locked = False
    ws.Range("B4,K4:K6,L4:L6,K8,C54,H54,M54,D:D,E:E,J:J,I:I,N:N").Locked = True
    ws.Protect
Next ws
End Sub

Hope that helps.

 

[aacod]

How do I unprotect all the sheets, once I protect it with this code? Cause I forgot to mention that I have Macros which donot work after protection. the code for the macro is:

Sub RED1()
'
' RED1 Macro
' Macro recorded 3/24/2009 by Staff
'
'
Selection.Interior.ColorIndex = 3
End Sub

 

[schielrn]

yes that is what the bold part does:

ws.Range("B4,K4:K6,L4:L6,K8,C54,H54,M54,D:D,E:E,J:J,I:I,N:N").Locked = True

 

[aacod]

<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->
How do I unprotect all the sheets, once I protect it with this code? Cause I forgot to mention that I have Macros which donot work after protection. the code for the macro is:

Sub RED1()
'
' RED1 Macro
' Macro recorded 3/24/2009 by Staff
'
'
Selection.Interior.ColorIndex = 3
End Sub

 

[aacod]

I would like to unprotect the all the 31 sheets with a code and macro buttons to work when all the sheets are protected, if possible.

thanks.

 

[schielrn]

<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1><!-- / icon and title --><!-- message -->
How do I unprotect all the sheets, once I protect it with this code? Cause I forgot to mention that I have Macros which donot work after protection. the code for the macro is:

Sub RED1()
'
' RED1 Macro
' Macro recorded 3/24/2009 by Staff
'
'
Selection.Interior.ColorIndex = 3
End Sub

You could do something like:

Sub RED1()
'
' RED1 Macro
' Macro recorded 3/24/2009 by Staff
'
'
activesheet.unprotect
Selection.Interior.ColorIndex = 3
activesheet.protect
End Sub

Hope that helps.