How to Run a Private Sub with a Shortcut Key

[hrayani]

Hello Friends,

I want to run the below VBA code with a shortcut key CTRL +q which I can if the sub is not Private

Can somebody please let me know how to make it run with a shortcut key if I set it to private (if possible) ?

Private Sub unlockactivesheet()
If Sheets("INDEX").Range("A1") = "HUMAYUN-LAPTOP1N4CT93" Then
ActiveSheet.Unprotect Password:="merchant"
ActiveWindow.DisplayHeadings = True
End If
End Sub

Regards,

Humayun

 

[Jaafar Tribak]

Use the OnKey Method of the application:

To activate the keys combo:

Application.OnKey "^q", "unlockactivesheet"

To deactivate it:

Application.OnKey "^q"

If the Private unlockactivesheet routine is located in a Class Module, say the ThisWorkbook Module, you can do this::

Private Sub Workbook_Open()
    Application.OnKey "^q", Me.CodeName & ".unlockactivesheet"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "^q"
End Sub

EDIT:
Or better than the Open and BeforeClose events, use the Workbook_Activate and Workbook_Deactivate events respectively so it is only applied to the workbook where the code is located.

 

[hrayani]

Hi Jaafar,

Thanks for the reply
It is a normal module - not class module

I have placed the codes in the workbook open & close even but when I press CTRL + q it says below

 

[hrayani]

Hi Jaafar,

Thanks for the reply
The code is in the normal module - Not class module

After placing the lines you have provided in the workbook open & close event & after pressing the shortcut key CTRL + q it says