Run macro when enter key is pressed

[themoreiseeyou]

Hi

I would like a Sub to run every time the number in a particular cell changes. Could someone help me with the syntax?

The follwing does not work:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "M3" Then Call Search
End Sub


Thanks in advance

 

[MickG]

It works for me !!!

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/29/2018  7:26:51 AM  EST
If Not Intersect(Target, Range("M3")) Is Nothing Then
If Target.Cells.CountLarge > 1 Then Exit Sub
Call Search
End If
End Sub

 

[themoreiseeyou]

Hi

Your solution works if the number in the cell is changed manually. However, the number in cell "M3" is changed using the following formula: =IF(AD57=1,RANDBETWEEN(1,8))
When this formula is placed in cell M3 the Private Sub does not work. Have you any idea how I can overcome this?

Thanks in advance

 

[James006]

Hi,

You could test the Private Sub Worksheet_Calculate() event macro..

Hope this will help

 

[My Aswer Is This]

That's true. These type scripts only run when a manual change is made. To have it work on a formula change would require help from someone else here on the forum.

Hi

Your solution works if the number in the cell is changed manually. However, the number in cell "M3" is changed using the following formula: =IF(AD57=1,RANDBETWEEN(1,8))
When this formula is placed in cell M3 the Private Sub does not work. Have you any idea how I can overcome this?

Thanks in advance

 

[themoreiseeyou]

Hi

Could this sub be altered so that: if cell M3 contains the number 2 then "Call Search" whenever the enter key is pressed?

Thanks in advance

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 11/29/2018 7:26:51 AM EST
If Not Intersect(Target, Range("M3")) Is Nothing Then
If Target.Cells.CountLarge > 1 Then Exit Sub
Call Search
End If
End Sub

 

[My Aswer Is This]

Try this:
Now again the value 2 must be entered manually not as a result of a formula.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/29/2018  8:27:26 AM  EST
If Not Intersect(Target, Range("M3")) Is Nothing Then
If Target.Cells.CountLarge > 1 Then Exit Sub
If Target.Value = 2 Then
Call Search
End If
End If
End Sub