March 17, 2014

Assign Keyboard Shortcut to Macros Using Excel VBA

We can assign keyboard shortcuts to macros using Excel VBA. The process is simple, effective and convenient. Now you can display a message, perform calculations or clear data by pressing the keyboard shortcut.
In the process of learning how to assign keyboard shortcuts to macros we also learn more interesting aspects of Excel VBA.
When you record macros in MS Excel you are offered the possibility of assigning a keyboard shortcut. You may like to refresh your memory by recording a simple macro and assigning a keyboard shortcut!
We have shown three interesting examples of how to assign keyboard shortcuts to macros created using the Visual Basic for Applications editor.
The VBA code for each of the modules is given below:

VBA code for Module1:
Sub welcomemessage()
‘After assigning a name to our macro we give out a message using the Date and Time functions
MsgBox “Today is ” & Date & ” ” & Time
End Sub

Vba code for Module2:
Sub calculate()
‘We declare two variables i and LastRow using the Dim keyword without assigning any data type. MS Excel will take care of the data types.
Dim i, LastRow
‘Next we determine the last row in our worksheet where data has been entered
LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row
‘The looping process then uses the cells property to assign the product of cells in the ith row and columns two (B) and three (C) to the cell in the ith row and fourth column (D)
For i = 3 To LastRow
Cells(i, 4).Value = Cells(i, 2).Value * Cells(i, 3).Value
Next i

End Sub

VBA code for Module3:
Sub dataclear()
Dim i, LastRow
LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row
‘We use the looping process to delete or clear the data from cells in the the ith row and fourth column (D) of our worksheet
For i = 3 To LastRow
Cells(i, 4).Value = “”
Next i
End Sub

VBA code for ThisWorkBook where the keyboard shortcuts are assigned:
Private Sub Workbook_Open()
‘As soon as the workbook is opened the following shortcuts are assigned to each of the above macros
Application.OnKey “^w”, “welcomemessage”
Application.OnKey “^c”, “calculate”
Application.OnKey “^d”, “dataclear”
End Sub

Watch the video below to learn more about the topic:

3 thoughts on “Assign Keyboard Shortcut to Macros Using Excel VBA

  1. Please need your help.
    I have multiple sheets in a workbook however, i need the first worksheet to always open as default and all other worksheet very hidden.
    I have already created a form from which to call up specific worksheets, now what i want is that when i call up any worksheet, all other worksheets should be hidden while the called up worksheet should be active and visible, then from the active worksheet i should be able to go back directly to the default (first) worksheet So, the default worksheet, should act like application interface to all all other sheet. Lastly changes made on any sheet should not be saved.

  2. Please to add to my earlier request, i also want the return to the default worksheet to be executed through ESC key press

Comments are closed.