Can anyone tell me why this macro does not run when cell value changes

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then Call Refresh

End Sub

Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook

Barry

Hi Barry,

Changing the value of Entry!$K$2 will not trigger the Worksheet_Change event
for another worksheet. You would need a worksheet calculate event. However,
to do that you would need to save the value of $A$2 at another out of the way
cell and perform a comparison in the start of the calculate event to see if
it actually changed.

Another way is to place the Worksheet_Change on the Entry sheet using K2 as
the target.

--
Regards,

OssieMac


"Barry Lennox" wrote:

Can anyone tell me why this macro does not run when cell value changes

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then Call Refresh

End Sub

Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook

Barry

Hi OssieMac
Thanks
I tried K2 on entry sheet idea but that didn't work either
K2 =INDEX($J$4:$O$75,MATCH($P$6,$O$4:$O$75,0),2)
Can you send me code for your first suggestion using cell T2
What formula do I put into T2
Barry
"OssieMac" wrote:

Hi Barry,

Changing the value of Entry!$K$2 will not trigger the Worksheet_Change event
for another worksheet. You would need a worksheet calculate event. However,
to do that you would need to save the value of $A$2 at another out of the way
cell and perform a comparison in the start of the calculate event to see if
it actually changed.

Another way is to place the Worksheet_Change on the Entry sheet using K2 as
the target.

--
Regards,

OssieMac


"Barry Lennox" wrote:

Can anyone tell me why this macro does not run when cell value changes

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then Call Refresh

End Sub

Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook

Barry

Hi again Barry,

You don't put any formula into T2.
T2 is used by the event code to save the value that is in A2 after each time
A2 changes. That way you can then compare the new value in A2 with T2. If
changed then save the new value to T2 and run required code. If A2 matches T2
then don't run the code.

The following code belongs with the worksheet that has the value in A2 that
is being tested.

Private Sub Worksheet_Calculate()
'Must disable events otherwise will run again
'when A2 is saved to T2
Application.EnableEvents = False

If Range("A2") < Range("T2") Then 'Value has changed
Range("T2") = Range("A2") 'Resave new A2 value
Call Refresh
End If

Application.EnableEvents = True
End Sub


Because you are disabling events, if for any reason the code fails before it
turns events back on (can occur during testing) then all events remain turned
off until you close and restart xl. Therefore the following little sub is
handy to have. Simply keep it in any of your modules and you can run it from
the VBA editor to re-enable events if they get inadvertantly turned off due
to code failure.

To run from the VBA editor just click anywhere in the sub and press F5.

Private Sub re_enable_events()
Application.EnableEvents = True
End Sub


--
Regards,

OssieMac


"Barry Lennox" wrote:

Hi OssieMac
Thanks
I tried K2 on entry sheet idea but that didn't work either
K2 =INDEX($J$4:$O$75,MATCH($P$6,$O$4:$O$75,0),2)
Can you send me code for your first suggestion using cell T2
What formula do I put into T2
Barry
"OssieMac" wrote:

Hi Barry,

Changing the value of Entry!$K$2 will not trigger the Worksheet_Change event
for another worksheet. You would need a worksheet calculate event. However,
to do that you would need to save the value of $A$2 at another out of the way
cell and perform a comparison in the start of the calculate event to see if
it actually changed.

Another way is to place the Worksheet_Change on the Entry sheet using K2 as
the target.

--
Regards,

OssieMac


"Barry Lennox" wrote:

Can anyone tell me why this macro does not run when cell value changes

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then Call Refresh

End Sub

Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook

Barry

Hi OssieMac
Thanks. Getting closer.
I need to run this with multiple windows open.
The only thing is the macro runs on the Sheet "Entry"
I have tried naming A2 and T2 as ranges but that didn't work.
If I enter "Sheets("Sheet2").Select" in the code it works, but the sheet
"Entry" is deselected and I have two copies of "Sheet2"
If I add "Sheets("Entry").Select" later in the code, whenever I enter
anything in Sheet2 it changes to Entry sheet
Barry

"OssieMac" wrote:

Hi again Barry,

You don't put any formula into T2.
T2 is used by the event code to save the value that is in A2 after each time
A2 changes. That way you can then compare the new value in A2 with T2. If
changed then save the new value to T2 and run required code. If A2 matches T2
then don't run the code.

The following code belongs with the worksheet that has the value in A2 that
is being tested.

Private Sub Worksheet_Calculate()
'Must disable events otherwise will run again
'when A2 is saved to T2
Application.EnableEvents = False

If Range("A2") < Range("T2") Then 'Value has changed
Range("T2") = Range("A2") 'Resave new A2 value
Call Refresh
End If

Application.EnableEvents = True
End Sub


Because you are disabling events, if for any reason the code fails before it
turns events back on (can occur during testing) then all events remain turned
off until you close and restart xl. Therefore the following little sub is
handy to have. Simply keep it in any of your modules and you can run it from
the VBA editor to re-enable events if they get inadvertantly turned off due
to code failure.

To run from the VBA editor just click anywhere in the sub and press F5.

Private Sub re_enable_events()
Application.EnableEvents = True
End Sub


--
Regards,

OssieMac


"Barry Lennox" wrote:

Hi OssieMac
Thanks
I tried K2 on entry sheet idea but that didn't work either
K2 =INDEX($J$4:$O$75,MATCH($P$6,$O$4:$O$75,0),2)
Can you send me code for your first suggestion using cell T2
What formula do I put into T2
Barry
"OssieMac" wrote:

Hi Barry,

Changing the value of Entry!$K$2 will not trigger the Worksheet_Change event
for another worksheet. You would need a worksheet calculate event. However,
to do that you would need to save the value of $A$2 at another out of the way
cell and perform a comparison in the start of the calculate event to see if
it actually changed.

Another way is to place the Worksheet_Change on the Entry sheet using K2 as
the target.

--
Regards,

OssieMac


"Barry Lennox" wrote:

Can anyone tell me why this macro does not run when cell value changes

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then Call Refresh

End Sub

Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook

Barry

Hi again Barry,

I think that I am confused about what you want to occur. I thought that if
A2 changes then you want the macro to run. From your last post I am assuming
that A2 is on Sheet2. Is this correct or not?

I also thought from the formula you posted that Sheet2 A2 changes every time
Sheet Entry K2 changes. Is this correct?

If you want the macro to run every time Sheet2 A2 changes then if you have
placed the macro in Sheet2 then what does it matter what is the active sheet?
Did you place the macro in Sheet2 module? You should right click Sheet2 tab
and select View Code to ensure you are placing it in the correct module.

You said that you have multiple windows open. Do you mean multiple Workbooks
open or multiple instances of Excel open?

Perhaps you can give me a step by step description as follows.
What conditions should trigger the macro?
What occurs in the sub Refresh? (Perhaps post the sub)



--
Regards,

OssieMac


"Barry Lennox" wrote:

Hi OssieMac
Thanks. Getting closer.
I need to run this with multiple windows open.
The only thing is the macro runs on the Sheet "Entry"
I have tried naming A2 and T2 as ranges but that didn't work.
If I enter "Sheets("Sheet2").Select" in the code it works, but the sheet
"Entry" is deselected and I have two copies of "Sheet2"
If I add "Sheets("Entry").Select" later in the code, whenever I enter
anything in Sheet2 it changes to Entry sheet
Barry

"OssieMac" wrote:

Hi again Barry,

You don't put any formula into T2.
T2 is used by the event code to save the value that is in A2 after each time
A2 changes. That way you can then compare the new value in A2 with T2. If
changed then save the new value to T2 and run required code. If A2 matches T2
then don't run the code.

The following code belongs with the worksheet that has the value in A2 that
is being tested.

Private Sub Worksheet_Calculate()
'Must disable events otherwise will run again
'when A2 is saved to T2
Application.EnableEvents = False

If Range("A2") < Range("T2") Then 'Value has changed
Range("T2") = Range("A2") 'Resave new A2 value
Call Refresh
End If

Application.EnableEvents = True
End Sub


Because you are disabling events, if for any reason the code fails before it
turns events back on (can occur during testing) then all events remain turned
off until you close and restart xl. Therefore the following little sub is
handy to have. Simply keep it in any of your modules and you can run it from
the VBA editor to re-enable events if they get inadvertantly turned off due
to code failure.

To run from the VBA editor just click anywhere in the sub and press F5.

Private Sub re_enable_events()
Application.EnableEvents = True
End Sub


--
Regards,

OssieMac


"Barry Lennox" wrote:

Hi OssieMac
Thanks
I tried K2 on entry sheet idea but that didn't work either
K2 =INDEX($J$4:$O$75,MATCH($P$6,$O$4:$O$75,0),2)
Can you send me code for your first suggestion using cell T2
What formula do I put into T2
Barry
"OssieMac" wrote:

Hi Barry,

Changing the value of Entry!$K$2 will not trigger the Worksheet_Change event
for another worksheet. You would need a worksheet calculate event. However,
to do that you would need to save the value of $A$2 at another out of the way
cell and perform a comparison in the start of the calculate event to see if
it actually changed.

Another way is to place the Worksheet_Change on the Entry sheet using K2 as
the target.

--
Regards,

OssieMac


"Barry Lennox" wrote:

Can anyone tell me why this macro does not run when cell value changes

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then Call Refresh

End Sub

Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook

Barry

Because the target cell is a formula, the change event doesn't occur when
cells change during a recalculation.
--

Regards,
Nigel




"Barry Lennox" wrote in message
...
Can anyone tell me why this macro does not run when cell value changes

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then Call Refresh

End Sub

Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same
workbook

Barry