Hitting "Cannot run the macro" error when using Application.OnKey

[shaidyn]

Hi, Folks,

I'm a VBA neophyte, bumbling my way through what I'd hoped would be a simple test program. I've created a sub (Test1) that compares a user entered number with a randomly generated number, and if they match declares them a winner. I then created a Command Button that, when clicked, will run this sub. Lovely.

I then tried to user Application.OnKey inside a Worksheet_Change sub such that when "Enter" is pressed, the Test1 sub will fire. Unfortunately, when I press the button, I get the "Cannot run the macro..." error. I've enabled macros, and since the Command Button macro works just fine, I'm not sure what the problem could be. I've included both a screenshot and the code, so any help is appreciated.

Thanks!

Sub CommandButton1_Click()

Test1

End Sub

Sub Test1()
Dim numberEnteredByUser As Integer
numberEnteredByUser = Cells(1, 1).Value

If numberEnteredByUser < 1 Or numberEnteredByUser > 10 Then
    MsgBox "Enter a number between 1 and 10."
    Exit Sub
End If

Dim RandomNum As Integer
Randomize
RandomNum = Int((10 - 1 + 1) * Rnd + 1)

Cells(2, 1) = RandomNum

If numberEnteredByUser = RandomNum Then
    Cells(4, 1).Value = "Winner!"
    Cells(1, 7) = Cells(1, 7) + 1 'Update the Win total
Else
    Cells(4, 1).Value = ""
    Cells(2, 7) = Cells(2, 7) + 1 'Update the Losses total
End If

Cells(1, 1) = ""

End Sub

Sub Worksheet_Change(ByVal Target As Range)
'Application.OnKey "{RETURN}", "thing"
Application.OnKey "{ENTER}", "Test1"

End Sub

 

[Jerry Sullivan]

Welcome to MrExcel,

The code for Sub Test1 should be in a Standard Code Module (you have it in a Sheet Code Module). Insert a Standard Code Module in your VBA project by Right-Clicking on the VBA Project > Insert > Module.

Also, you might not want to have your Application.OnKey statement inside a Worksheet_Change event. Once the Enter key has been mapped to the macro Test1, the additional calls each time there is a change on your worksheet have no benefit.

You may want to create macro that runs the OnKey statement once at the beginning of the game then toggles to clear that key mapping at the end of the game.

 

[shaidyn]

Thank you for your swift reply! I was able to get a bit further along, but I seem to have run into the same problem.

What I've done is set things up so that if the user clicks in a specific cell (A1), then the Enter key's functionality changes. If they click elsewhere, it returns to default:

Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Address(False, False) = "A1" Then
        MsgBox "enter active"
        Application.OnKey "{RETURN}", "Module1.Switch Target"
        Application.OnKey "{ENTER}", "Module1.Switch Target"
    Else
        MsgBox "enter disabled"
        Application.OnKey "{RETURN}"
        Application.OnKey "{ENTER}"
    End If
    
End Sub

I've moved my test code into Module1:

Sub Switch(ByVal Cell As Range)

    If Target.Address(False, False) = "A1" Then
        MsgBox Cell.Address
    End If
    
End Sub

Unfortunately, when I click on cell A1 and press enter, I get the same error as before: "Cannot run the macro..." Is it because of the "Target" parameter? I did my research online and I thought I had the syntax correct.

Thanks again.

 

[Jerry Sullivan]

There's a few problems including the use of Target in Module1 where it has no scope (meaning).

If your Application.OnKey toggling works as you intended, Switch Sub will only get called if the selected cell is Sheet1!A1.
Given that, is there any reason you need to pass a range parameter to your Switch Sub?

Your code will need to toggle back to the default key mapping if the user leaves Sheet1 of this workbook without triggering the Worksheet_SelectionChange event.
That will happen if the user changes sheets or changes active workbooks. I'd suggest you hold off on trying to address that until you get your code working for a user that stays on Sheet1.