Search code examples
vbaexcelevent-handlingkeypress

Excel: Running Another Macro on KeyUp

I am trying to make a user form and there is a macro to show this. I want this macro (which shows the user form) to run when I press (keyUp) a key lets say Shift or Capslock keys only.

I found a post at the below for the same but it is showing error when i run it.

https://stackoverflow.com/a/14209798/1279872

Please see the attached image for the error message:

https://www.dropbox.com/s/8thupki17ajqde7/Picture1.png?dl=0

The vba code i am using is (Both procedures are on the same sheet)

Private Sub Workbook_Open()
    Application.OnKey "{CAPSLOCK}", "testing"
    MsgBox ("test2")
End Sub

Sub testing()
    MsgBox ("test1")
End Sub

The excel can be downloaded from the following link (if you want to see the same):

https://www.dropbox.com/s/c277skeywsrkbzy/Book1.xlsm?dl=0

Solution

  • In ThisWorkbook you need to put:

    Private Sub Workbook_Open()
    Application.OnKey "{CAPSLOCK}", "testing"
    MsgBox ("test2")
End Sub

    The sub testing should be in a module.