PDA

View Full Version : Call macro by typing "Enter"



jack nt
01-18-2014, 08:09 AM
in a sheet's codes:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$A$1": Application.OnKey "~", "Macro1"
Case Else: Application.OnKey "~", "Macro2"
End Select
End Sub

in a modul, there are macros


Sub Macro1()
'write your codes here
MsgBox "Hello!"
End Sub

Sub Macro2()
'write your codes here
MsgBox ActiveCell.Address & ": " & "Hi ya!"
End Sub

now if Cells("A1") is chosen, <enter> we get "Hello", others get ActiveCell.Address and "Hi ya!"

ashleyuk1984
01-18-2014, 10:28 AM
This is the help section... Where you usually ASK for help.
So are you asking a question? Or are you giving other people information on how to do this?
I don't understand.

jack nt
01-21-2014, 07:47 AM
sorry about that. the question is: how can i use application.onkey once, no need typing another key to terminate onkey.

SamT
01-21-2014, 08:33 AM
how can i use application.onkey once, no need typing another key to terminate onkey.

You can't.

Try this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$A$1": Application.OnKey "~", "Macro1"
Case Else: Application.OnKey "~", "Macro2"
End Select
End Sub

Sub Macro1()
Application.OnKey "~"
MsgBox "Hello!"
End Sub

Sub Macro2()
Application.OnKey "~"
MsgBox ActiveCell.Address & ": " & "Hi ya!"
End Sub

Or This:
Sub Macro3()
OnKeyClear_Tilde
MsgBox "Hello!"
End Sub

Sub OnKeyClear_Tilde()
Application.OnKey "~"
End Sub

Sub OnKeyClear_Tab()
Application.OnKey "[TAB}"
End Sub

jack nt
01-21-2014, 08:36 PM
Sub OnKeyClear_Tab() Application.OnKey "[TAB}" End Sub
there are so many sheets in my workbook, so i use one sheet as menu listing name of all other sheets.
when choose and enter, the chosen sheet is selected. it works, but application.onkey still fires when i type {enter} at the other sheets. i want it automatic cancels after call marcro.

now i use api for this and done. but i still like to use onkey, it seems simpler (?)

westconn1
01-22-2014, 03:31 AM
i want it automatic cancels after call marcro.
samt posted code above, to do just that

snb
01-22-2014, 04:45 AM
You'd better state what you are trying to accomplish.

Browsing to sheets can be done easily using the rightbutton when hovering in the left down corner of the Excel sheet.
Another method would be creating hyperlinks in your 'summary' sheet.
In neither case any VBA, let alone API, is required.

jack nt
01-22-2014, 07:22 AM
thank you all