application.OnKey not working as Add-in

[as0921]

the application.onkey worked before creating it as an add-in now the only way the command will work is if I go into VBA project screen and run the sub. How do I get it to run without running the a sub in VBA projects.


--------- code "this workbook"
Option Explicit
Public Sub Workbook_Activate()
Application.OnKey "^+s", "Autofilter"
End Sub

Public Sub Workbook_Deactivate()
Application.OnKey "^+s"
End Sub

-------- code "Module 1"
Option Explicit
Public Sub Autofilter()
Dim FinalRow As Long
Dim FinalColumn As Long
Dim PRange As Range
Application.OnKey "^+s", "Autofilter"
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & FinalRow).Autofilter
End Sub

 

[Jaafar Tribak]

Try placing the OnKey code in the addin workbook Open event instead of the Activate event but then the key will remain hooked throughout

Are you trying to activate the key only when a specific workbook is active ? if so then you will have to hook the Application events upon loading the adding via the use of the WithEvents Keyword.

 

[as0921]

Yes, want this shortcut work on any workbook that is open. With your suggestion I change activate to Open and it did not do anything.

Option Explicit
Public Sub Workbook_open()
Application.OnKey "^+s", "Autofilter"
End Sub

Public Sub Workbook_Deactive()
Application.OnKey "^+s"
End Sub

----- Module

Option Explicit
Public Sub Autofilter()
Dim FinalRow As Long
Dim FinalColumn As Long
Dim PRange As Range
WSD.Application.OnKey "^+s", "Autofilter"
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & FinalRow).Autofilter
End Sub