Have enter trigger macro if certain cell is selected

[scotty562]

So what I have a button that when clicked does some stuff after what they are looking for is typed into a cell. I know the users will want to be able to type in what they are looking for and hit the enter key and have it accomplish the same thing as clicking the button (it opens a drop down list).

I've been searching on this for a few hours now and I got close with OnKey, but I can't get that working reliably. It seems like you can't turn it on and off like I want to be able to. Or maybe I just can't figure out how to.

Anyway what I'm looking for is a reliable way to have the enter keys on the keyboard trigger my macro when cell K4 is selected. I want the enter keys to work normally otherwise. The drop down list is a data validation cell if that helps.

 

[mole999]

worksheet_change and target intersect combined should give you what you want

 

[scotty562]

That does turn on associating the enter key with the macro which is how I was working with it, but every time the enter key is pressed after that will still trigger the macro. OnKey can be turned off, but I just haven't been able to get it to work properly. I'd much rather prefer another option if there is one.

 

[Fluff]

Something like

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Address = "$K$4" Then Exit Sub
  'your code here
End Sub

 

[My Aswer Is This]

Would something like this do what you want.
Modify what you want the script to do.
Mine just pops up a message box

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-25-18 4:00 PM EDT
If Not Intersect(Target, Range("K4")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
MsgBox Target.Value
End If
End Sub

 

[scotty562]

It looks like both of those options run code when K4 was changed for any reason. The end result of this would be the script would trigger after they hit enter, clicked anywhere on the sheet, ect. I'd rather have them have to click on a button than this, but it is certainly close to what I'm looking to do. What I could do is set this up and have the button to click on as well and just train them they have to click the button. I'll have to think about it and play with it.

What I'm looking for is almost like having Excel watch every key press and do stuff once it sees enter pressed. If Excel could natively assign keys to macros that don't need ctr + shift, that would be ideal.

 

[My Aswer Is This]

It looks like both of those options run code when K4 was changed for any reason. The end result of this would be the script would trigger after they hit enter, clicked anywhere on the sheet, ect. I'd rather have them have to click on a button than this, but it is certainly close to what I'm looking to do. What I could do is set this up and have the button to click on as well and just train them they have to click the button. I'll have to think about it and play with it.

What I'm looking for is almost like having Excel watch every key press and do stuff once it sees enter pressed. If Excel could natively assign keys to macros that don't need ctr + shift, that would be ideal.

This script only runs when they change a value in Range("K4"
It does not run when they enter a value in any other cell. Or press the enter key in any other cell.