keypress to fire command button

M

mikewild2000

How do i assign the "q" key to operate my "commandbutton1" code?

Do i need to assign the same code to the "q" key, if so how again
 
J

John Wilson

mikewild2000,
How do i assign the "q" key to operate my "commandbutton1" code?
Click to expand...
Can't be done.

but...............................

Create a new macro in a regular module.
For arguments sake, name it "MyCommButtCode"
Now cut and paste everything in the CommandButton1
code (between the Sub & End Sub) and paste it into
this new sub.
In the CommandButton1 code, call the new sub

Private Sub CommandButton1_Click()
MyCommButtCode
End Sub

Now, from the VBA editor in the Immediate window, type the following:
Application.MacroOptions Macro:="MyCommButtCode", Description:="",
ShortcutKey:="q"
and then press <enter>.

Even a little easier................
Record a macro (any name) and assign the shortcut key.
Delete the code in that macro, replace it with your CommandButton
code and then just call that macro from your CommandButton.

John
 
M

mikewild2000

It is a workaround.

But what about copying the code and assigning a keypress event to it?
Is that possiable.
 
J

John Wilson

mikewild2000,
It is a workaround.
Click to expand...
That it is and since it wasn't what you were looking for, I dug
a little deeper.

First of all, forget all that drivel I wrote about moving code
around. I don't know why, but the following did, in fact, run
the CommandButton_Click code (Excel 2000):

ActiveSheet.CommandButton1.Value = True

The above is assuming that you have the CommandButton
on a worksheet and that it was created from the Controls Toolbox.

Create a macro with your shortcut key assigned and use the
code above in that macro to "press" the button.

Is that any closer to what you need??

John
 
D

Dave Peterson

Another way is to call the procedure.

I had this under sheet1 (and sheet1 is the codename of the worksheet):
Option Explicit
Sub CommandButton1_Click()
MsgBox "hi there"
End Sub

(notice that it doesn't have "private" in front of the Sub statement.

And in a general module:

Option Explicit
Sub test()
Call Sheet1.CommandButton1_Click
End Sub
 
M

mikewild2000

Thanks Dave - I got their in the end by copy/paste my code from the
command buttons into seperate macros.

Then using workbook_open()
poped in the onkey statements so that the shortcut keys work.

Not bad for a novice?
 
D

Dave Peterson

If it's working, you've lost the novice label.

mikewild2000 < said:
Thanks Dave - I got their in the end by copy/paste my code from the
command buttons into seperate macros.

Then using workbook_open()
poped in the onkey statements so that the shortcut keys work.

Not bad for a novice?
Click to expand...
 
Top