Trigger worksheet_change event

hmk

Active Member
Joined
Jun 8, 2004
Messages
423
Hi there
This problem is headach :mad:

In sheet2 A1 i put this formula = sheet1 A1

so whenever i type a value in sheet1 A1, then it will appear in the sheet2 A1.........through the formula in (=sheet1!A1) which is placed in sheet2 A1

i put a private worksheet_change in sheet2
my problem is that the private worksheet_change is not triggered when the value of A1 in sheet2 changed

How to trigger a private code when results appear through built-in formulas or functions ?
I know that This event occurs when the value of a cell is changed. This event does not occur when the value is changed as the result of a calculation.

Is there any why around it to trigger the event ?
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
helmekki.

On Sheet2, use Private Sub Worksheet_Calculate().

A nice tester is to insert MsgBox "Changed!" here and to change your cell value on Sheet1.

hope that helps! ben.
 

hmk

Active Member
Joined
Jun 8, 2004
Messages
423
sweater_vests_rock, thank u very much

but the private code was not triggered by the result appeared in sheet2

need something to trigger the private code when results on sheet2 when the values results from formulas or built-in functions

Any idea how to trigger ?
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
helmekki.

i'm not sure what you mean. are you saying this does something, but not what you want, or it does nothing?

if what you tried did nothing, follow the steps below:
-------------------------------------------------------------------------------------
Set up a new workbook like so.

Cell A1 on Sheet2:
=Sheet1!A1

Cell A1 on Sheet1:
1

In the VB Editor, goto the Project SideBar. Select Sheet2 (under VBA Project (Book1) \ Microsoft Excel Objects)

On this screen then, change the first drop-down list from General to Worksheet. Change the second drop-down list to Calculate. You should now see

Code:
Private Sub Worksheet_Calculate()

End Sub

Insert the message box
MsgBox "You will see this when you change the value of Cell A1 on Sheet1"

Now you should have
Code:
Private Sub Worksheet_Calculate()
MsgBox "You will see this when you change the value of Cell A1 on Sheet1"
End Sub

Close the VB Editor and change the value of Cell A1 on Sheet1.

When you do this, you will see the message box pop-up. If you do not, goto Tools->Options->Calculation Tab->Automatic Toggle On

So, put whatever code you want to be triggered upon the change of Cell A1 into this sub.

-----------------------------------------------------------------------------------

If the code did something but not what you want, can you try again to explain? I didn't follow your last post.

ben.
 

hmk

Active Member
Joined
Jun 8, 2004
Messages
423
Thank u very much sweater_vests_rock

i mean that the private module was not triggered by the result of the formula on the sheet2, until u move the cureser to any other cell which means that the movement of the curser it self is considered a trigger

only the private module can deal with the formula result if there is a trigger to trigger the private module which is in this case the movement of the courser to any other cell

i used
Code:
 Private worksheet_SelectionChange ()

and put Range("").Offset().Select

so when the courser moved to a different cell, then the private module is triggered and red the cell A1 value.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

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
Top