CODEWITHSUNDEEP
vbaexcel
PootyToot
PootyToot

Reputation: 329

Application.OnKey Procedure error - Cannot run Macro

I am working on a module which uses an onkey event to detect if backspace or delete is pressed, if it meets the criteria for the event, the corresponding cells will clear contents.

I have a procedure to launch the corresponding sub, but I get an error 'the macro cannot be found'

I have attached an image of the error and the code below, any suggestions?

Image of error enter image description here

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column = 2 Then
   Application.OnKey Key:="{DEL}", Procedure:="DeleteResources"
   Application.OnKey Key:="{BACKSPACE}", Procedure:="DeleteResources"
End If
End Sub

Public Sub DeleteResources()
ActiveCell.Offset(, 1).ClearContents
ActiveCell.Offset(, 2).ClearContents
ActiveCell.Offset(, 3).ClearContents
End If
End Sub

Upvotes: 4

Views: 4306

Answers (2)

Rory
Rory

Reputation: 34045

Just as an FYI, since you already have a working solution, you don't actually have to move the routine, you just need to prefix the name with the code name of its container module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim sProc As String
    sProc = Me.CodeName & ".DeleteResources"
    If ActiveCell.Column = 2 Then
       Application.OnKey Key:="{DEL}", Procedure:=sProc
       Application.OnKey Key:="{BACKSPACE}", Procedure:=sProc
    End If
End Sub

Public Sub DeleteResources()
    ActiveCell.Offset(, 1).Resize(, 3).ClearContents
End Sub

Upvotes: 2

joehanna
joehanna

Reputation: 1489

You need to put DeleteResources into a code Module so that it has full public scope, particular when you use late-binding of procedure names.

Further, you need to remove the extra End If you have in that method.

Upvotes: 4

Related Questions