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.
Insert a command button in excel
  • Click on the Command Button icon in the Form Controls.
Insert a command button in excel drag and drop method
  • 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.
how to code the command button in ms excel using vba editor
  • This opens the VBA editor.
infographics macro using vba editor

Writing the Code of Command Button using VBA Editor

The VBA editor would be opened. It would contain

how to code the command button in ms excel using vba editor step 2
  • 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
using a message box in vba

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.
using command button in excel vba
message box in vba

This brings us to an end.

Thank you for reading.

Leave a Comment