Unlock cells conditionally on previous cells

[mg4345]

Hi,

This is my first post but I'm amazed at the speed of response others have got so I thought I'd give it a try!

To spell it out as plainly as I can:

To start I want cells F6:H16 to be locked.

Then when either "existing" or "install" is input into E6:E16, I want F6:F16 to unlock

Then when either "existing" or "install" is input into F6:F16, I want G6:G16 to unlock

Then when either "existing" or "install" is input into G6:G16, I want H6:H16 to unlock

It's fair to say I'm a beginner macro user so please don't assume much in explaining what I should do!

Thanks very much in advance

[VegasNath]

Welcome to the Lounge Matt!

I imagine that this *is* possible, but this is a little over my head. I'll have a play around and see if I can come up with anything (no promises)

Hopefully, one of our resident guru's will be along soon.

[StuartR]

You could do this by using a Worksheet_Change event and macro code that looks at which cell(s) have changed and locks / unlocks the corresponding cells to the right (which is what the code in your worksheet is trying to do).

This method has a number of disadvantages, the worst of which is that it completely disables Excel's undo feature.

An alternative approach is to change the source of the data validation list for each cell, depending on what has been entered in the cell to its left. This wouldn't "lock" the cell but it would prevent any value from being entered other than those you expect.

If you change the Data Validation list for cell F6 from
=$R$5:$R$7
to
=IF(ISNA(MATCH(E6,$R$5:$R$6,)),$R$7,$R$5:$R$7)
Clear the validation Ignore Blank checkbox
Then copy this cell and Paste Special > Validation to the rest of cells F6:H16 I think it will do what you want. Have a look at the attached spreadsheet and see if it works for you.

[attachment=85439:Calculat..._08_09MG.xls]

[stans]

That's neat