Runing a macro on by changing a value of the cell

[luv2glyd]

Is there a way to change the value of cell A1, for example, from one value to another, hit the enter key, and activate a macro this way.

A couple of qualifications:
- the value in cell A1 will never be the same (that is it could be -23.43 or .12)
- I can not simply tie the macro to the "enter" key as there will be numerous cells (A1, B3, C90 and so on) in the same sheet that I will need to do this with, each tied to a different macro.

Thanks for your help.

[Phil_V]

Right click on the worksheet's tab, View Code.

Paste the following code:

Please Login or Register  to view this content.

Change cell A1 and you'll get the message, change cell F3 and you'll get the other message. Any other cell does nothing. This code certainly isn't polished or bullet-proof, but it should give you the idea to start with

[luv2glyd]

that gets me closer! The only trouble is when I delete the value in any other cell, say B6, the first message appears....

[rylo]

Hi

Try this.

Please Login or Register  to view this content.

Note that if you highlight say A1:B3 and delete, you won't get any message. It is currently set to only action on single cells.
rylo

[luv2glyd]

great, that works!

If I wanted to call the same macro with changes in cells A1 and B1 and C4, what code would I need to insert in the 3rd line?

Thanks

[rylo]

Hi

Sorry, don't follow. Do you want it to action if you have a block of cells selected, or do you wish to nominate additional single address locations?


rylo

[luv2glyd]

Both, actually, but if there is no way of doing that in the same line or two, then a block.

[rylo]

Hi

Put this code in place.

Please Login or Register  to view this content.

Now if you change A1, you will get the first message. Now highlight the range A1:B5 and press delete, you will get the second message.

Does that cover your options?

rylo

[Phil_V]

Please Login or Register  to view this content.

The alteration to Rylo's code in line 3 above is how you specify the same thing to happen for several different cells, (changed individually), and as Rylo said, the final section (involving the intersect) is for when you change a block of cells including your trigger cell.

Alternatively if you don't want to differentiate between whether it was affected as a single cell or part of a block then the whole thing will (I think) be a lot simpler, something like:

Please Login or Register  to view this content.

[Phil_V]

It's probably worth pointing out that in my last example, because of them being 'Elseif' statements only 1 statement would trigger.

For example if you had data in the whole range of A1 to F3, then selected the whole lot and pressed delete, you will only get the message for cell A1 changing.
If you wanted it to trigger for each of the cells that had changed on a block change you would need to change each 'Elseif' to be an 'If' and also insert an 'End If' on the line above each one.

[luv2glyd]

Exactly what I needed. Thank you very much, guys.

[luv2glyd]

one follow up questions to what we just did. The code above will be used when manually adjusting the cell values. I'll also have another macro that resets these same cells to a certain value by clicking a button.

How would I temporarily disable the code above while my other macro is running?

Thanks

[VBA Noob]

Which code?

You didn't say who's code worked

VBA Noob

[luv2glyd]

For now, I'll be using the following:

Please Login or Register  to view this content.

I need to disable it while other code runs.

[VBA Noob]

Try

Please Login or Register  to view this content.

VBA Noob

[luv2glyd]

Great, thank you!