Conditional Protection

[Robert]

Hi,

I have a question about setting up my workbook to do conditional
protection. I have searched the internet for quite some time trying to
find a solution to this problem.

As background, our users want to populate a 12 month forecast with
actuals from the complete month, and they want to be able to key in a
number for the future months.

Here's the situation: on tab1, called Instructions, I have a cell B9
that stores a value "Y" or "N". This corresponds to whether January
should be populated with Actuals or not.

On another tab, tab4, called Plan, if the value in Instructions!B9 is
Y, then column J is populated from the Actuals tab, but if the value
in Instructions!B9 is N, the cell is populated with 0.

I am able to accomplish everything with If statements through normal
Excel formulas. The thing I'm missing, though, is I would like to be
able to protect the column if it's being populated with Actuals so
that the user cannot overwrite the values that are populated from the
Actuals tab.

I have seen a very close example of this, so I think it's possible,
but I haven't been able to get it to work, and also I am confused
about a few points of coding. If someone can show me an example of how
to code this, I would very much appreciate it, especially if they
would comment it well enough for me to follow it.

Here's the sort of things that confuse me as a total newbie:

1. Where do I put the code?
2. How do I refer to a value on another tab of the worksheet?

Regards,

Robert Sparkman

 

[PA]

Hi Robert,

What you want (if I understood right) is to have the column J in your tab
"Plan" locked only if B9 on "instruction" contains "Y" Other then that,
column J will be unlocked.

In order to react to cell changes by code, you need to work with events.
This page offers a good introduction to the subject (and a lot of other
stuff):

http://www.cpearson.com/excel/Events.aspx

Here is some code for you. Right click on the "Instruction" tab and choose
"View Code" then paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)

'We want to do something only if the cell that was changed
'is B9
If Target.Address <> "$B$9" Then Exit Sub

On Error GoTo ErrorHandler

Application.ScreenUpdating = False

Dim shtPlan As Worksheet

Set shtPlan = Sheets("Plan")

'Unprotect the sheet to be able to change the locked property
shtPlan.Unprotect

If Target.Value = "Y" Then
shtPlan.Range("J1").EntireColumn.Locked = True
Else
shtPlan.Range("J1").EntireColumn.Locked = False
End If

ErrorExit:
On Error Resume Next
shtPlan.Protect
Set shtPlan = Nothing
Application.ScreenUpdating = True
Exit Sub

ErrorHandler:
MsgBox "An error occured:" & vbNewLine & _
Err.Description

Resume ErrorExit

End Sub

There's not so much comments, but try it and ask me your questions.

PA

 

[Robert]

Hi,

Thanks a lot for your help.

Actually it worked very well.

Here's the code I came up with...my solution was a bit more complex
than I presented because I also needed to do the same thing for
January - December with the Y/N value in different cells.

I would appreciate any suggestions on improvements as I am not totally
sure how the error handling works.

Regards,

Robert
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

'Dim Variables
Dim shtPlan1 As Worksheet
Dim shtPlan2 As Worksheet
'Define variables
Set shtPlan1 = Sheets("Plan")
Set shtPlan2 = Sheets("Plan - Interco")

'Conditional protection for Actual months on the forecast pages
Select Case Target.Address

'January section
Case "$B$10"
'Errorhandling
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("J1").EntireColumn.Locked = True
shtPlan2.Range("K1").EntireColumn.Locked = True
Else
shtPlan1.Range("J1").EntireColumn.Locked = False
shtPlan2.Range("K1").EntireColumn.Locked = False
End If

'February section
Case "$B$11"
'Errorhandling
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("K1").EntireColumn.Locked = True
shtPlan2.Range("L1").EntireColumn.Locked = True
Else
shtPlan1.Range("K1").EntireColumn.Locked = False
shtPlan2.Range("L1").EntireColumn.Locked = False
End If

'March section
Case "$B$12"
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("L1").EntireColumn.Locked = True
shtPlan2.Range("M1").EntireColumn.Locked = True
Else
shtPlan1.Range("L1").EntireColumn.Locked = False
shtPlan2.Range("M1").EntireColumn.Locked = False
End If

'April Section
Case "$B$13"
'Errorhandling
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("N1").EntireColumn.Locked = True
shtPlan2.Range("O1").EntireColumn.Locked = True
Else
shtPlan1.Range("N1").EntireColumn.Locked = False
shtPlan2.Range("O1").EntireColumn.Locked = False
End If

'May section
Case "$B$14"
'Errorhandling
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("O1").EntireColumn.Locked = True
shtPlan2.Range("P1").EntireColumn.Locked = True
Else
shtPlan1.Range("O1").EntireColumn.Locked = False
shtPlan2.Range("P1").EntireColumn.Locked = False
End If

'June section
Case "$B$15"
'Errorhandling
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("P1").EntireColumn.Locked = True
shtPlan2.Range("Q1").EntireColumn.Locked = True
Else
shtPlan1.Range("P1").EntireColumn.Locked = False
shtPlan2.Range("Q1").EntireColumn.Locked = False
End If

'July section
Case "$D$10"
'Errorhandling
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("R1").EntireColumn.Locked = True
shtPlan2.Range("S1").EntireColumn.Locked = True
Else
shtPlan1.Range("R1").EntireColumn.Locked = False
shtPlan2.Range("S1").EntireColumn.Locked = False
End If

'August section
Case "$D$11"
'Errorhandling
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("S1").EntireColumn.Locked = True
shtPlan2.Range("T1").EntireColumn.Locked = True
Else
shtPlan1.Range("S1").EntireColumn.Locked = False
shtPlan2.Range("T1").EntireColumn.Locked = False
End If

'September section
Case "$D$12"
'Errorhandling
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("T1").EntireColumn.Locked = True
shtPlan2.Range("U1").EntireColumn.Locked = True
Else
shtPlan1.Range("T1").EntireColumn.Locked = False
shtPlan2.Range("U1").EntireColumn.Locked = False
End If

'October section
Case "$D$13"
'Errorhandling
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("V1").EntireColumn.Locked = True
shtPlan2.Range("W1").EntireColumn.Locked = True
Else
shtPlan1.Range("V1").EntireColumn.Locked = False
shtPlan2.Range("W1").EntireColumn.Locked = False
End If

'November section
Case "$D$14"
'Errorhandling
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("W1").EntireColumn.Locked = True
shtPlan2.Range("X1").EntireColumn.Locked = True
Else
shtPlan1.Range("W1").EntireColumn.Locked = False
shtPlan2.Range("X1").EntireColumn.Locked = False
End If

'December section
Case "$D$15"
'Errorhandling
On Error GoTo ErrorHandler
'Turn screen updating off
Application.ScreenUpdating = False
'Unprotect sheets to update Locked property
shtPlan1.Unprotect
shtPlan2.Unprotect
'Lock or unlock column depending on value
If Target.Value = "Y" Then
shtPlan1.Range("X1").EntireColumn.Locked = True
shtPlan2.Range("Y1").EntireColumn.Locked = True
Else
shtPlan1.Range("X1").EntireColumn.Locked = False
shtPlan2.Range("Y1").EntireColumn.Locked = False
End If

Case Else
'Do nothing
End Select

'Error handling
ErrorExit:
On Error Resume Next
shtPlan1.Protect
shtPlan2.Protect
Set shtPlan1 = Nothing
Set shtPlan2 = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox "An error occurred:" & vbNewLine & _
Err.Description
Resume ErrorExit

End Sub