RePost - Calling a Macro from a key

[RWN]

Given I haven't received any response I assume that my original post (quoted below) was
not clear so I'll attempt to re-phrase the question.

How can I execute a macro when the user hits the "+" sign on the numeric keypad?

Original post;

I'd like call a macro (to re-position the cursor) when the "+" sign is pressed on the
numeric keypad.
After looking through the help and "playing around", the closest I can come is using an
"OnKey" statement but I can't get it to;

a) recognize the keypad "+"
b) call a macro, *even* when I give it a key that it can recognize.

ex, using the keyboard "+" sign.

I tried putting the Application.OnKey "{+}", "macroname" in a workbook open event and in
the Workbook module I put the "macroname" sub.
(The close event resets the key.)

What I get is "filename!macroname not found" (at least I know it's re-assigned the key!)

So I have two problems (not to mention my lack of understanding)
1st - I'm not structuring the logic correctly (it can't locate the macro) and,
2nd - I have no idea what the code is for the keypad "+" sign.

Direction?

 

[Jim Cone]

Rob,

" I have no idea what the code is for the keypad "+" sign"

This works for me...
Application.OnKey "{107}", "ColorCell"

Regards,
Jim Cone
San Francisco, USA

 

[Gary Brown]

I don't believe that you can distinguish between the keyboard and the keypad
PLUS SIGN using OnKey.I do believe that the syntax you are using is correct,
however.You may need to be more specific with your macro.For
example...Instead of Application.OnKey "{+}", "MyMacro"You may have to
sayApplication.OnKey "{+}", "MyWorkbook!MyMacro"HTH,Gary Brown

 

[RWN]

Jim;
Thanks for that.
Now, if I can test your patience further, I'm stuck on how the call works.

I put the sub (ColorCell) in sheet1.
When I hit "+" I get an error message "The macro 'sheet1!ColorCell' cannot be found"
What am I missing (and I know I'm going to feel stupid!)?

 

[Jim Cone]

Rob,

Did you see Gary Brown's post?
Also, put the code in a regular/general module and try it.

Regards,
Jim Cone

 

[RWN]

Thanks Gary;
Jim Cone gave me the key code (107) - and that works.

I've posted back to him that I cannot get the macro to execute however.

Your post gave me the idea that I should be qualifying the macro name, so I used;

Application.OnKey "{107}", "TestIT!ColorCell"

Now get the error message -> The macro "C:\dirname\[TestKeyCall.xls]TestIt'!ColorCell'
cannot be found

(I didn't expect the "!" to appear inside the single quote though??)

In the TestIt sheet I have;

Sub ColorCell()
Beep
End Sub

 

[RWN]

BINGO!!
Put it in a general module and it worked, without any qualification at all.

If I don't re-set the key and close the workbook then it still works on any
workbook/sheet-not that I'd want to do that!
(it opens the book that the macro is in-magic.)

I think the light is coming on, thanks to the two of you!

Now, off to play.

Again, thanks to you both - I'm a better person now.

 

[Guest]

Rob,

Not sure if you still need an answer to this problem. I was trying to do
something similar, followed the help you received here, and receive the same
error.

The problem (at least for me) was that I was trying to call a function by
pressing a key. As far as I know this isn't possible. However, if you
record a macro then replace the macro (module) code with the code you want
executed then it works.

For instance, my call was Application.OnKey "~","NextEntry"
I recorded a macro named NextEntry. Then I edited the macro in VBA and
replaced the macro code I had recorded with the code I wanted to be executed.

Hope this helps you or someone else.

Mike

Thanks Gary;
Jim Cone gave me the key code (107) - and that works.

I've posted back to him that I cannot get the macro to execute however.

Your post gave me the idea that I should be qualifying the macro name, so I used;

Application.OnKey "{107}", "TestIT!ColorCell"

Now get the error message -> The macro "C:\dirname\[TestKeyCall.xls]TestIt'!ColorCell'
cannot be found

(I didn't expect the "!" to appear inside the single quote though??)

In the TestIt sheet I have;

Sub ColorCell()
Beep
End Sub


--
Regards;
Rob
------------------------------------------------------------------------

I don't believe that you can distinguish between the keyboard and the keypad
PLUS SIGN using OnKey.I do believe that the syntax you are using is correct,
however.You may need to be more specific with your macro.For
example...Instead of Application.OnKey "{+}", "MyMacro"You may have to
sayApplication.OnKey "{+}", "MyWorkbook!MyMacro"HTH,Gary Brown

 

[RWN]

Mike;
Thanks for the reply.
Actually got it working using the following code in the Workbook module.

Private Sub Workbook_Open() 'Set "+" numeric kpd to call macro to allow
Application.OnKey "{107}", "DownHome" 'moving cursor to next line,col1 on entry
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{107}", ""
End Sub

The "DownHome" macro is in a general code module that handles an order entry process.

Sub DownHome()
Set Asht = ActiveSheet
If Asht.Name = "OrderEntry" Then 'Ensure that this is the order entry sheet
and,
Cells(ActiveCell.Row + 1, 1).Select 'if so, go down one line and home to Col A
End If
End Sub

This was done as an entry aid so that the user doesn't have to leave the numeric keypad to
get to the next line.
She enters the phone #, product # etc. and the numeric "+" to shift to the next order.

Again, thanks for the interest.

--
Regards;
Rob
------------------------------------------------------------------------

Rob,

Not sure if you still need an answer to this problem. I was trying to do
something similar, followed the help you received here, and receive the same
error.

The problem (at least for me) was that I was trying to call a function by
pressing a key. As far as I know this isn't possible. However, if you
record a macro then replace the macro (module) code with the code you want
executed then it works.

For instance, my call was Application.OnKey "~","NextEntry"
I recorded a macro named NextEntry. Then I edited the macro in VBA and
replaced the macro code I had recorded with the code I wanted to be executed.

Hope this helps you or someone else.

Mike

Thanks Gary;
Jim Cone gave me the key code (107) - and that works.

I've posted back to him that I cannot get the macro to execute however.

Your post gave me the idea that I should be qualifying the macro name, so I used;

Application.OnKey "{107}", "TestIT!ColorCell"

Now get the error message -> The macro "C:\dirname\[TestKeyCall.xls]TestIt'!ColorCell'
cannot be found

(I didn't expect the "!" to appear inside the single quote though??)

In the TestIt sheet I have;

Sub ColorCell()
Beep
End Sub


--
Regards;
Rob
------------------------------------------------------------------------

I don't believe that you can distinguish between the keyboard and the keypad
PLUS SIGN using OnKey.I do believe that the syntax you are using is correct,
however.You may need to be more specific with your macro.For
example...Instead of Application.OnKey "{+}", "MyMacro"You may have to
sayApplication.OnKey "{+}", "MyWorkbook!MyMacro"HTH,Gary Brown