In this lesson, we train Macro in Excel using VBA scripting. You can do different tasks automatically by writing MACRO. In this lesson, we create a simple macro that changes the value of a cell by clicking on a button. Finally you learn how to add a button in Microsoft Excel.
As default setting of Microsoft Excel, there is no Developer tab, to add the Developer tab, follow these steps:
Right-click on where there is no special button and click customize the ribbon and in the section.
In the popup window and in the right drop-down list, select Main tabs. Then check the Developer Checkbox and click OK.
You can easily see that Developer has been added to your ribbon bar.
Now you should learn how to add a button in Microsoft Excel. To create a button or command button, click the Insert button on the same Developer tab. Then select the shape of the command button in the form below in the Activex Controls group.
To assign a macro (one or more lines of the VBA code), this button should right-click on CommandButton1, the same button that was created, and select View Code.
Be sure to enable the Design Mode button on the Developer tab.
This will open the Visual Basic editor. We need to write our code between the two statements Private Sub CommandButton1_Click () and End Sub statement. People who have already worked the Visual Basic language are familiar with these sentences. If you are not one of those people, do not worry, we’ll explain it. Complete the coding according to below figure.
Tip: The left window of the names sheet1, sheet2, and so on … is called Project Explorer; if you do not see it, click on the View tab on Project Explorer.
For coding for Sheet1, you should click on Sheet1 to open the corresponding Coding window. As you can see, we wrote the Range (A1) in the Sheet1 coding window. Each of our screens has this range. Because of writing inside the coding window of Sheet 1, this is the same A1 range in Sheet 1.
Close the editor and click on the button which you created. Make sure the Design Mode is disabled. In fact, when Design Mode is enabled, clicking on the button is for design purposes, and Excel chooses button for you to make custom edits. If the Design Mode is disabled, clicking the button will execute the code associated with the click. Also right-clicking will execute the code for the right click that you wrote it yourself.
You can see the result. We were able to execute the code by clicking on the button that you built in Excel. We learn to write more complex code little by little. You learned How to add a button in Microsoft Excel.
Visual Basic editor
If you need to directly open the coding environment in Excel, follow the path below.
Developer —> Visual Basic —> Visual Basic editor