Trigger worksheet_change event

[hmk]

Hi there
This problem is headach

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 ?

 

[sweater_vests_rock]

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]

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]

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

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

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]

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

 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.