Event On Enter

[ExHell]

Does anyone no a way to trigger a macro by just using the enter key
in a difined rage ("A1:A100")
Not worksheetchange

 

[Peter_SSs]

Does anyone no a way to trigger a macro by just using the enter key
in a difined rage ("A1:A100")
Not worksheetchange

I'm not quite sure what you mean by "Not worksheetchange", but wouldn't this do what you want?


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("A1:A100")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">' Whatever code you want to run goes here (or macro name)</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[ExHell]

I diddn't wan't a worksheet_Change event
in some instance's the data wont be changed
i was looking at excuting a macro just by hitting the enter key

 

[pbt]

I diddn't wan't a worksheet_Change event
in some instance's the data wont be changed
i was looking at excuting a macro just by hitting the enter key

Just a thought:

Could you not just place a Button on your sheet and assign your macro, (whatever that is) to the button. Then by hitting (clicking) the Button, the macro would run.

Harry

 

[Legacy 98055]

Yes there is a way, but Why? There may be another approach that you do not know of.

 

[ExHell]

Thanks right click but i thought this forum had a purpose

 

[Legacy 98055]

In workbook class.

Option Explicit
 
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
 
Private Sub Workbook_Open()
    GetAsyncKeyState vbKeyReturn
End Sub

In the worksheet.

Option Explicit
 
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
        If GetAsyncKeyState(vbKeyReturn) Then
            MsgBox "Enter key pressed within range A1:A100"
        End If
    End If
End Sub

<a href="http://home.fuse.net/tstom/0627082337.1608245.zip"><img src="http://home.fuse.net/tstom/zip.gif"width="48"height="48"border="0"></a> <a href="http://home.fuse.net/tstom/0627082337.1608245.zip">0627082337.1608245.zip</a>

 

[mikerickson]

How about putting Peter's code in the Selection_Change event, which triggers even if no value is changed?
One could use a static range variable and probe the Application.MoveAfterReturnDirection to determine if Enter was pressed, or the mouse used to click a cell.

 

[Smitty]

Thanks right click but i thought this forum had a purpose

You're treading on real thin ice there, pal.

Were you maybe thinking of the SelectionChange event?

 

[pbt]

Being the Devil's advocate on this;

Keep in mind that normally when you (or your user) hit the enter key, you will move down to the next cell.

Yes there is a way, but Why? There may be another approach that you do not know of.

It might help to know what your macro is going to do with or without a change on the sheet.

Harry

Sorry: I didn't see all the "Pro's" comming on to this post. I think their replies are far better then my post. Just was trying to narrow down something.