How to Add a VBA Button in Excel?
VBA is a human-readable, editable programming language that lets you automate your daily task flow.
How to Add a VBA Button in Excel?
Since you have already begun your journey to become an Excel wizard, there's something that you might have constantly heard - "Hey, can you write a VBA code?"
A VBA, or Visual Basics for Applications, is a human-readable, editable programming language that lets you automate your daily task flow.
Suppose you daily download a file from a third-party website that needs to be cleaned before being uploaded to the database. Cleaning the data could be as simple as removing empty rows and columns, deleting unnecessary values, etc.
Well, if the task you perform every day won't differ, why not automate it?
The VBA allows you to write a programming code which can then be run with just a single click of a button.
Many thought processes go into writing such codes, but that doesn't necessarily mean it's complicated.
However, this article will focus on a simple objective - how to add a VBA button in Excel.
Key Takeaways
- Visual Basic for Applications (VBA) streamlines repetitive tasks, enabling one-click automation.
- Activate the Developer tab by customizing the Ribbon, allowing access to VBA functionalities.
- Visualizing task flows before coding is essential for creating structured VBA code, even for seemingly simple tasks.
- Adding a button simplifies VBA code execution, offering a direct and time-saving method compared to navigating menus.
- Explore alternatives like ActiveX controls and assigning macros to shapes for added flexibility in executing VBA tasks.
Enabling the Developer Tab
If you have never written a VBA code before and don't know how to begin, you first need to enable the Developer tab from Ribbon. If you haven't allowed the Developer tab, you might find the Ribbon below:
Right-click on any of the tabs and click on Customize the Ribbon
This will open up the dialog box as illustrated below:
On the right-hand side, you will find the check box for the Developer tab. Just tick the check box and click on Ok. Next, you will find a new account when you recheck the Ribbon.
Now that you have enabled the Developer tab, you can begin writing a VBA code as simple as 'Hello World' to more complicated programs that simultaneously work on ten Excel models.
Remember that when writing VBA code, you won't just be limited to Excel itself, but you can also integrate different applications with Excel to run an automated process.
For example, the PowerAutomate application will take queries as to what time to run the task, let's say at 11:00 AM, automatically open the Excel file, click on the macro Button and save the output file in a different folder.
This eliminates human intervention, while all that's left to do is analyze the code-generated output file.
Visualizing and writing the VBA code
Before you wrote the code, you might have thought about what task you wanted to automate—for example, copying the data from Excel A to Excel B.
It helps when you visualize the flow of processes that would be needed to automate the task. For example, the copying of data from Excel A to Excel B would include the following steps:
- Locating and opening the Excel file A.
- Landing on the correct spreadsheet that consists of the required data.
- Selecting the range which consists of the required data.
- Copying the data.
- Locating and opening the Excel file B.
- Choosing and opening the correct spreadsheet in Excel File B.
- Select the cell or range of cells to paste the data.
- Paste the copied data.
- Close both Excel files (optional).
As you can see, even a simple code of copying data from one source to another requires deep thinking as to how things should lay out to carry out the task successfully.
But as we always say, it gets much easier once you practice and understand the basics behind the code.
You did not start walking right away when you were born. Over time, you fell, hurt your head, and finally, after a couple of steps, you believed that you could walk.
Working on VBA would be similar if you do not have a programming background. But, unfortunately, I did not, and the maximum I took to understand the basics was a couple of weeks, after which I could review and even write VBA codes on my own.
Let's say you wrote the code. How do you run it now?
Running the VBA code using Button
If you just finished writing a code, the immediate window that you might be seeing is as illustrated below:
To run the code now, click Run > Run Macro or press the F5 Button.
This will run the entire code until the end. If you want to debug the code or run one line of code at a time, you can press the F8 key.
Is that efficient daily?
Imagine opening the file > clicking on the Developer tab > Selecting the Module > Run the code.
No thanks, it would take too much time. I would instead copy the data directly and follow all these steps.
A hack that you can use to run the VBA code directly is by adding a button in Excel.
Click on the Developer tab > Insert and select the Button under the Form Controls.
This will allow you to draw a rectangular figure on the spreadsheet, after which a dialog box opens:
This is the same code you wrote to copy the data from one file to another. Finally, click on Ok, after which you will see the changes in the spreadsheet as
You can now use this Button to directly run the VBA code rather than going to the Developer's tab.
Suppose you have multiple VBA codes that perform a varied task. Naturally, it would be confusing if all were named 'Button 1', 'Button 2'....and so on.
You can rename the Button by right-clicking on the Button> Edit Text which lets you edit the name for each Button.
As we already said, now all you need to do is click on the Button after you open the file so that the macro automatically runs for you and copies the data from file A to file B.
Alternatives to a VBA button
Some might have a question - "Wasn't there a similar icon for the button in the Insert under the Developer tab?"
a) ActiveX controls
You are right. That's another way to add a Command button in Excel that falls under the ActiveX controls.
So, what is the difference between both available options?
The biggest difference between both functions is that ActiveX Control items allow you to input the VBA code separately to work on a task rather than separately writing the code and assigning it to a button.
If you see a button that says = EMBED("Forms.CommandButton.1",""), you have selected the Command button from ActiveX controls.
Like a normal button, you can customize the ActiveX controls button by right-clicking on the item and selecting the properties.
This should open up the dialog box, as illustrated below:
The different options include renaming the dialog box and changing the text font, size, and even color. You can also change the background, enable word wrap or even insert an image into the Button!
Do remember about the 'Design Mode' that gets enabled whenever you try to make any customizations that won't allow you to run the code.
b) Different shapes in Excel
If you like to be creative and don't want to stop yourself from improving the macro, Excel allows you to different input shapes to which the VBA codes can be assigned.
Click on Insert > Select the Shapes or Icons from the Illustrations section.
We will go ahead and click on Icons which opens up the dialog box, as illustrated below:
Let's say you just wrote a code that finds a particular value in the entire Excel spreadsheet. What icons come closest to the objective of the macro?
There are two that match our objective - one which microscope, and the other is the eye.
After selecting either, we click on Insert, and the spreadsheet adds the icon.
Now, all you need to do is right-click the icon and then click on Assign Macro. A few next steps would be the same as how you assign a macro to a normal button.
or Want to Sign up with your social account?