21, Feb, 2019

How to insert a button in Excel (VBA)

excel vba

You can insert buttons inside an excel document to perform certain predefined functions to make your work easier. The functions can be formulas, formatting, printing certain cells or range etc.

Before you insert a button, you have to write its function inside the VBA editor (modules), we will use an example of Addition Function (add values of 2 cells and print it inside another cell) to explain it.

Example 1: Addition with Button

Go to Developer Tab and click on Visual Basic to open up the VBA Editor

Then Go to Insert-> Module

Then add a Subroutine called Addition_example( ) as shown in below picture

Then add following lines of Code in between Sub and End Sub

Dim A As Integer ‘Use A as variable

Dim B As Integer ‘Use B as variable

Dim C As Integer ‘Use C as variable

A = ThisWorkbook.Sheets(“Sheet1”).Range(“A” & 1)  ‘ variable A takes the value of cell A1

B = ThisWorkbook.Sheets(“Sheet1”).Range(“B” & 1) ‘ variable B takes the value of cell B1

C = A + B  ‘ variable C adds the values of A and B

ThisWorkbook.Sheets(“Sheet1”).Range(“C” & 1) = C ‘ variable C prints its value to cell C1

The above code adds values in Cell A1 and B1 then print the answer in C1,

The final module will be something like shown in below image

You have written your Subroutine for Addition, now we have to insert the Button to call this routine.

Go back to Developer Tab then Insert -> Button (as shown in below image)

Then Locate the position of your button by clicking anywhere in excel and drag the cursor.

As you can see, the button has appeared in your excel sheet.

Select the Macro “Addition_example” to link your routine to the button and press OK.

The job is done!  It’s time to check it. Just type any number in A1 and B1 cells then press the Button.

To change the name of your button (from Button 1 to ADD), Right click on the button and edit text

As shown below,

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>