CODEWITHSUNDEEP
excelvbaevents
j2associates
j2associates

Reputation: 1155

How do I resolve this error when trying to Implement Application.OnKey

I am trying to use Application.OnKey to capture Ctrl+Shift+N and Ctrl+Shift+P to navigate through the sheets. This is proof-of-concept. When fully implemented other processing will take place inside the methods.

The code currently resides in a macro enabled template (*.xltm). When a template file is opened and saved as an xlsm, everything appears to work normally. When the saved xlsm file is opened, the "allow macros dialog" appears as expected. But when I try to use Ctrl+Shift+N I get an error dialog:

Cannot run macro fullpath\FileName.xlsm!ProcessNext. The macro may not be available in this workbook or all macros may be disabled.

Here is my code:

    Option Explicit
    
    Private Sub Workbook_Open()
        ' Next sheet Ctrl+Shift+N.
        Call Application.OnKey("^+N", "ProcessNext")
        ' Prior sheet Ctrl+Shift+P.
        Call Application.OnKey("^+P", "ProcessPrior")
    End Sub
    
    Private Sub ProcessNext()
    Dim lIndex As Long
        With ThisWorkbook
            lIndex = ActiveSheet.Index
            If lIndex = .Sheets.Count Then
                lIndex = 1
            Else
                lIndex = lIndex + 1
            End If
            .Sheets(lIndex).Activate
        End With
    End Sub
    
    Private Sub ProcessPrior()
    Dim lIndex As Long
        With ThisWorkbook
            lIndex = ActiveSheet.Index
            If lIndex = 1 Then
                lIndex = ThisWorkbook.Sheets.Count
            Else
                lIndex = lIndex - 1
            End If
            .Sheets(lIndex).Activate
        End With
    End Sub

Upvotes: 1

Views: 119

Answers (1)

exception
exception

Reputation: 326

Put your ProcessNext() and ProcessPrior() macros in a separate code module (Insert | Module) to expose them in the global context...

Declare them as "Public":

Public Sub ProcessNext()

Public Sub ProcessPrior()

Upvotes: 2

Related Questions