Conditional protection question

[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

 

[Shane Devenshire]

Hi,

You will need code here is a sample

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B9"))
If Not isect Is Nothing Then
If Target = "Y" Then
Sheets("Sheet3").Protect
Else
Sheet("Sheet3").Unprotect
End If
End If
End Sub

1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it. This is the Instruction sheet.
3. Paste in or type the code above.

You will need to unlock all cells you want open to data entry before you use
the macro. You will also need to specifiy your sheet, not Sheet3. You may
want to enhance the macro in other ways.