call macro via formula

[bstubbs]

first time - forgive errors.

I want to compare the values of two cells and if not the same, run a macro. I'm trying to not have to click any buttons or use Excel to manually run the macro. Any ideas?

[DonkeyOte]

Assuming the 2 values are on the same sheet you can use a Worksheet Event -- which one will depend upon how the 2 values are being altered/updated ... if they are updated manually then I would advocate the Worksheet_Change event else the Worksheet_Calculate event to invoke your subsequent sub routine... using the former is preferable given it will be invoked fewer times.

Let us know.

[bstubbs]

I am new to Excel and when you say "Worksheet event", I don't know for sure what you're talking about. Do we have a tutorial that will show me something about it? If you could also prototype some code for me, once I figure out the event, it would be a tremendous help. TIA

[DonkeyOte]

If you could elaborate with regard to my earlier Q as to how the 2 values in question are "altered" that would help - as the event you need to use will differ -- if the values are altered by means of formulae you will need to use a Calculation event, if they are altered manually you will use the Change event.

[royUK]

Have a look at this. It is triggered by changing A1 or B1

[modytrane]

Roy,
your macro is triggered by changing A1 OR A2.
didn't want bstubbs to get confused.
modytrane.

[royUK]

Well spotted. Thanks

Code triggered by A1 & B1

Please Login or Register  to view this content.

[bstubbs]

Ok - I'm trying to work through the concept and understand - a problem for me sometimes. ;->

I right-click on the page of the workSheet involved and select "code". Enter the code prototyped. Then, assuming no bugs have hatched and I typed correctly, this code would auto-magically execute whenever those two cells are the same. Big picture - is this correct?

Thanks, again.

Bob

[shg]

I right-click on the page of the workSheet involved and select "code"

View Code

Then, assuming no bugs have hatched and I typed correctly, ...

Don't type it, copy and paste from the post.

this code would auto-magically execute whenever those two cells are the same.

The code will execute whenever either A1 or B1 is changed manually (not as the result of a formula).

Your code (MyMacro) will be called if the cells are DIFFERENT. If you want it to be called when the are the same, change <> to =

[bstubbs]

One last question for you: how would the code be changed to use a single-cell named range from the spreadsheet rather than an explicit address?

Thanks, again.

Bob

[shg]

Right now it looks specifically at A1 and B1 and tests whether they are equal. I don't understand the logic you would want to apply with a single-cell range.

[bstubbs]

If the cell location within the underlying spreadsheet changes, then I would also have to change to hard-coded address of the cells to be evaluated. If, however, the reference was to a named-range, that would presumably work even with the new location of the cell. Or am I missing something on the way that the VBA would work?

TIA.

Bob

[shg]

If, however, the reference was to a named-range, that would presumably work ...

Of course, that's not the question.

how would the code be changed to use a single-cell named range

The current code checks if TWO cells are equal each time EITHER of them changes.

How do you translate that logic to ONE cell?

[bstubbs]

I would be comparing the values within two cells, not just a single one. What I'm envisoning is the comparison of two single-celled named-ranges instead of two hard-coded single-cell addresses.

[shg]

Got it. Maybe this:

Please Login or Register  to view this content.

... where cell1 and cell2 are your two single-cell named ranges.

[bstubbs]

Thanks for all the help and understanding of newbie status.

Bob