Worksheet_change doesn't fire when linked value changes ?

ClaesB

New Member
Joined
Mar 19, 2002
Messages
5
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
On your Worksheet Menu Bar
Click Tools
Options
Calculation Tab

Check your workbook Options at the bottom...
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top