Conditional Protection
 

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

Conditional Protection
 

If Plan sheet column J contains formulas that return numbers from Actuals
sheet, just protect Plan sheet.

Formulas will work but users cannot edit the cells manually.

Is that what you need?

If you want cells column J to be editable when a cell is 0 then you would
need event code.


Gord Dibben MS Excel MVP

On Thu, 5 Mar 2009 06:16:13 -0800 (PST), wrote:

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