Application.OnKey Procedure error - Cannot run Macro

Question

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

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

Answer 1

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.

Answer 2

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