Write a Macro using VBA Editor – Command Button

Today, we will learn to write the VBA code of a macro using the VBA editor. However, we can record a macro without having the need to code. Excel adds some extra lines of code when we record a macro and sometimes, we need the code to execute control structures or loops.

Writing code for macro using the VBA editor helps us as it makes code compact and we can use looping and conditional statements in the macro.

So let us begin learning.

Example – Coding of Button in Excel

In this tutorial, we are going to learn how to add certain lines of VBA code to a button in ms excel. The code will run when the button is clicked.

This VBA code would perform the intended instructions in our workbook through the VBA code. Let us say we want to display a message box when the button is clicked.

You can use the following practice workbook to practice along with us.

Inserting a Command Button

We can insert a command button in our spreadsheet by following these steps.

• Go to the Developer tab on the ribbon. Add the developer tab if it is not present on the ribbon.
• In the Controls group, click on the Insert button.

• Click on the Command Button icon in the Form Controls.

• Drag your mouse pointer to shape the button. When you release the mouse click, Assign Macro dialog box would appear. Click on New to code a new macro from the VBA editor.

• This opens the VBA editor.

Writing the Code of Command Button using VBA Editor

The VBA editor would be opened. It would contain

• A new module named Module 1 would be created in the workbook.
• Module 1 contains code for the starting and end of the command button when it is clicked.
• The object is a command button having the name Button1 and the event is click(). The macro name is Button1_click(). This macro runs when Button1 is clicked.
• We need to code in the Button1_click().
• The End sub tells the ending of the VBA code.

Now, we would display a message using the MessageBox function.

Enter the following line of code.

Sub Button1_Click()
MsgBox "Welcome Back to Project", vbOKOnly + vbExclamation, "Hey!"
End Sub

vbOKOnly displays an Ok button in the message box when we run the button1_click() and vbExclamation puts an exclamation icon before the message, “Hey” is the title of the message box.

Running the Code through Command Button

Now we would run this code through the command button.

• Hold the alt key and then press the F4 key to close the VBA editor.
• Click anywhere in the Sheet Area.
• Press the Command Button in the worksheet.

This brings us to an end.

Thank you for reading.