Is there a better way to call macro on cell value change?

**skysurfer** · 06-01-2010, 02:32 PM

Hi,

I currently have a macro that checks one cell ("ChangeTrigger") for a change in formula value on a non-active sheet and if there is a change it then fires another macro ("Macro99"). Problem is that while this macro works, it only seems to work for a change in ONE celI and I need it...or better code...to be able to check for a value change in 3 cells and run one of three different macros depending on which cell had a change in value.

My current code is on the worksheet object:

Please Login or Register  to view this content.

And this little pice of code is atop a module:

Please Login or Register  to view this content.

Thanks,

Lawrence

**Bob Phillips** · 06-01-2010, 05:46 PM

Try this

Please Login or Register  to view this content.

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

**skysurfer** · 06-01-2010, 07:32 PM

Bob,

That code looks very promising. Thank you!

Two questions: Will the Change event work for a formula result on a non-active worksheet? And how do I get a change in H3 and H5 to fire different macros? for example, if H3 changes, fire Macro99B; and if H5 changes, fire macro99C.

Thanks again,

Lawrence

**Bob Phillips** · 06-02-2010, 04:09 AM

Originally Posted by skysurfer

Will the Change event work for a formula result on a non-active worksheet?

I am not sure what you mean by this, can you elaborate?

Originally Posted by skysurfer

And how do I get a change in H3 and H5 to fire different macros? for example, if H3 changes, fire Macro99B; and if H5 changes, fire macro99C.

You have to test each cell individually for an intersection and call the appropriate macro.

**teylyn** · 06-02-2010, 06:08 AM

skysurfer,

a Worksheet_Change event macro only monitors its own worksheet, not other sheets. You need Change events in each sheet to trigger activities in each sheet.

A Worksheet_Change event also only works on certain events. If a cell value changes as a result of a formula, this does not trigger the Worksheet_Change.

But the Worksheet_Calculate event would be triggered by a formula that recalculates.

So, if A1 has a formula, you either monitor the precedents of cell A1 with a Change event, or monitor A1 with a Calculate event.

cheers

**DonkeyOte** · 06-02-2010, 06:16 AM

Originally Posted by teylyn

a Worksheet_Change event macro only monitors its own worksheet, not other sheets.
You need Change events in each sheet to trigger activities in each sheet.

... or use the Workbook level SheetChange Event (using the Sh variable to determine appropriate action(s))

**teylyn** · 06-02-2010, 07:14 AM

DO, good point. I have not used that event much. Would it catch formula recalcs, for example if the precedents are in another workbook?

**DonkeyOte** · 06-02-2010, 07:21 AM

The Workbook Change event would only capture changes undertaken on the active sheet (or active sheets if grouped).

The Workbook Calculate event would trigger for each sheet requiring calculation as and when calculation was invoked (by whatever means)

I confess I'm not quite sure what you're getting at regards external precedents ?

**teylyn** · 06-02-2010, 08:28 AM

not quite sure what you're getting at regards external precedents ?

Just trying to come up with a scenario where a change in a cell is not triggered by a manual entry, but by a reference to an external workbook, for example.

Say A1 references to Z1 in another workbook. B1 in the current book performs a calculation, referencing A1. If Z1 in the external file is changed, A1 in the current file will update and B1 will recalculate.

This will then trigger the Sheet Calculate event. Does it also trigger the Workbook Calculate event? -- I'll go and set up a test scenario tomorrow, then I'll know.

**romperstomper** · 06-02-2010, 08:34 AM

The Workbook doesn't have a Calculate event, so no.

It will, however, trigger the Workbook_SheetCalculate event...

**skysurfer** · 06-02-2010, 12:08 PM

Originally Posted by teylyn

skysurfer,

a Worksheet_Change event macro only monitors its own worksheet, not other sheets. You need Change events in each sheet to trigger activities in each sheet.

A Worksheet_Change event also only works on certain events. If a cell value changes as a result of a formula, this does not trigger the Worksheet_Change.

But the Worksheet_Calculate event would be triggered by a formula that recalculates.

So, if A1 has a formula, you either monitor the precedents of cell A1 with a Change event, or monitor A1 with a Calculate event.

cheers

teylyn,

So, modifying my original Calculate event code posted in the OP works? How do I get that code to monitor three cells instead of just one?

[sorry, I'm not that gifted with vba]

Lawrence

**teylyn** · 06-02-2010, 06:40 PM

Lawrence,

Take another look at Bob Phillips' code. It's all in there!

cheers

**skysurfer** · 06-02-2010, 08:55 PM

Originally Posted by teylyn

Lawrence,

Take another look at Bob Phillips' code. It's all in there!

cheers

Cool! Will his Intersect below line work with Worksheet_Calculate? If so, then I'm golden!!!

Please Login or Register  to view this content.

Thanks again,

Lawrence

**teylyn** · 06-02-2010, 10:01 PM

With the caclulate event you don't need to check for an intersect with Target. You can inspect the cells you want to inspect by referencing them directly.

Is there a better way to call macro on cell value change?

LinkBack

Thread Tools

Rate This Thread

Display

Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Re: Is there a better way to call macro on cell value change?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions