How to insert a button in Excel (VBA)

Submitted by CAAadmin on Wed, 07/20/2016 - 07:28
vba button excel

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

excel

Then Go to Insert-> Module

excel automation

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

excel subroutine

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

excel vba codig

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)

excel add button

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.

excel automate vba

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.

excel add values

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

As shown below,

excel automation

 

Tags