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:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.