using cell to lock/unlock another cell

[2funny4words]

The attached file is the worksheet that I am working on. I was told that if I could get around merging cells it would be to my benefit. I am hoping that if I can get around merging cell h7-ac7, I can use h7 and write code that locks/unlocks g12. I centered across selection and was able to not merge g12 but with h7, if I do the same thing, I can not view the validation list.

Basically I need to use h7 to control locking or unlocking g12 (if h7="Grass Fire" or "Timber Fire" unlock g12 or else lock g12. I have not had any luck at this. Maybe someone has an idea. Any other suggestions on the form would be appreciated. This is my first excel project where any type of code was written and I am unsure of where to go at this point.

Thanks in advance.

[Leith Ross]

Hello 2funny4words,

I deleted the Worksheet_SelectionChange event in your workbook since it is not needed. The code to unlock, write to, and lock "G12" was added to the Worksheet_Change event procedure. It is quite easy to deal to merged cells, once you know how. The changes have been added to the attached workbook. Here is the revised code...

Please Login or Register  to view this content.

[2funny4words]

Thanks Leith, the code works but does a little different than what I wanted. What would I have to change to make it unlock g12 if h7 is "Grass Fire" or "Timber Fire"? I assume that I would format g12 to be locked and then the code would unlock if needed but am unsure of how to change the code.

Basically, if there was a grass or timber fire and we respond, we have to give it a name. ie. "The little running fire", "the peanut fire", (any name works, it just has to have a name so that we don't confuse different fires with each other). On structure fires, car wrecks, ect. no name is needed so the cell would be locked.

Eventually I will require g12 to be filled in if unlocked before the save macro will run. (That code would be appreciated also but I can start another thread when this code is figured out.)

[Leith Ross]

Hello 2funny4words,

I misunderstood what you wanted to do. I assume you want the cell locked again after an entry has been in G12, yes?

[2funny4words]

Leith, g12 would not necessarily have to be locked after being filled in, but that would probably make my next code easier to write,

ie. "if g12 is locked allow save macro to run or else message (must fill in fire name)" not sure what exactly the code would look like but that would be the idea.

If g12 was locked after being filled out, What would be the method of changing g12 if it was accidentally typed wrong ie. tabbed out of cell before finishing the name, etc.?

[2funny4words]

Let me make this maybe a little easier. I used a vlookup formula to control the content of E9 (MERGED AREA). If g12 (MERGED AREA) needs to be unlocked, the content of E9 will be "WF". All I need then is a code to unlock g12 if content of E9 is "WF". I would like it to clear g12 if the content of E9 is changed to something else other than "WF"

Here is the code that I tried but nothing seems to happen.

Please Login or Register  to view this content.