Worksheet_change doesn't fire when linked value changes ?

[ClaesB]

I Have a case where I want a macro to start whenever a certain cells changes, regardless of reason for cell change (i.e. user input or link change.
According to Excel Help, Worksheet_Change() should be my ideal solution, but it doesn't seem to fire when the value in linked cells changes.
Workbook_Calculate was another shot I tried, but I can't see an easy way to determine whether my cells of interest are changed or not.

[Tom Schreiner]

Hi

This is fun

Eventhough I might be wrong

I do believe that links are governed by the Workbook and not individual sheet modules. Try this event here:
Notice (((WORKBOOK)))_SheetChange

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

[ClaesB]

Instant response !! Thanks !

I have tried workbook_change() also. The problem is that it does not seem to trig on links either. Example: On Sheet1 I have a cell (A2) containing a link to a cell (B2) on Sheet2. Changing the value in cell B2 on Sheet2 does fire workbook_change, but the target is Sheet2!B2.
I would need an event with target Sheet1!A2. But I cannot see that this happens.

Have also tried linking to another workbook, and changing the source in that workbook, does not trig the event (in the first wkb) at all.

Ok, I think I can solve my problem by using Worksheet_Calculate, but not without some dirty coding to determine if certain cell values are changed or not.

Btw, are there any Excel settings that might be set to disable event on links ?

/Claes

[Tom Schreiner]

This is the help file from Office 2000

SheetChange Event


Occurs when cells in any worksheet are changed by the user or by an external link.

Syntax

Private Sub object_SheetChange(ByVal Sh As Object, ByVal Source As Range)

object Application or Workbook. For more information about using events with the Application object, see Using Events with the Application Object.

Sh A Worksheet object that represents the sheet.

Source The changed range.

Remarks

This event doesn't occur on chart sheets.

[Tom Schreiner]

On your Worksheet Menu Bar
Click Tools
Options
Calculation Tab

Check your workbook Options at the bottom...

[Tom Schreiner]

Sorry, I misunserstood you

internal links(calculations) do not set off any change events. You will have to trap it in the calculate event like you already figured...

[Tom Schreiner]

I tried what you are trying to do and the macro did fire when Sheet2 B2 was changed by Sheet1 A2

Are you calling the macro correctly???

If not, you just simply list the macro name in the calculate_event.

[ClaesB]

I appreciate Your help !

Did you use Workbook_change ?
My eventcode looks like this:
-------------------
Private Sub Workbook_SheetChange(ByVal objSheet As Object, ByVal rngTarget As Range)
Dim wksSheet As Worksheet

Set wksSheet = objSheet

MsgBox ("Sheet:[" & wksSheet.Name & "] Range:[" & rngTarget.AddressLocal & "]")

Set wksSheet = Nothing

End Sub
-------------------

When I change the value in Sheet2!B2, the target in my event is Sheet2!B2.
I would like to get an event where target is Sheet1!A2.

/Claes

[Tom Schreiner]

Here is all I did

In the sheet where the change is being trapped.

Private Sub Worksheet_Calculate()
MacroNameHere
End Sub

That should be all you have to do.

Just in case were getting stuck.
When a value is typed into a cell, you must exit the cell before any events or calculations occur

A little more detail. I might have it backwards, but you'll get the jist of it

Here is my calc link

this formula is in cell Sheet2 B2
=Sheet1!A2

when i change the value in sheet1 A2 and exit the cell, sheet2 B2 calculates and runs your macro. The calculate event is in sheet 2?

I hope this helps

[ClaesB]

Well, when using the Calculate event my problem is not to get it fired, but to find out if the cells of interest,let's say Col 1, has changed or not.

The original case was to run a macro (that plays a sound), whenever certain cells (Column 1) is changed.

By using the calculate event I guess I have do some coding to determine if cells are changed or not (i.e. save prev values in hidden column, and then compare upon calcuate event)

Ok, this might be the only solution, but it would be nice to find an easier one ...

/Claes