As a Business Analyst or Data Analyst, you’ve likely encountered the need for VBA Macros to automate repetitive tasks in Excel, such as creating date sequences, scheduling and extracting reports. One of the most effective ways to execute these tasks is by using command buttons that trigger the macros you've written.
In this article, we'll explore three essential methods for creating a Macro Button in Excel: Form Buttons, ActiveX Buttons, and Shape Buttons. We'll also cover how to insert a Macro Button into the Quick Access Ribbon for even easier access.
There are 3 types of Buttons in Excel.
1. Form Buttons
2. ActiveX Buttons
3. Shape Button
| Enable Developer Tab |
| Insert ActiveX Buttons in Excel Sheet |
| Insert Form Buttons in Excel Sheet |
| Insert Shap Button in Excel |
| Insert Macro Button in Quick Access Ribbon |
Enable Developer Tab :
- Click on Visual Basic.
- In Excel, we have Input Date in Cell A2 and Range Value in C2 Cell.
- Based on these two parameter, VBA will execute the code from VBA Editor.
Insert Form Buttons in Excel Sheet:
- In the Developer tab, click Insert and select Button (Form Control).
- Place the button on your Excel sheet.
- Excel will prompt you to assign a macro for Button1. Select the macro from the list and click OK.
- You can edit the button name and assign the macro according to your needs.
Sub Add_Date_Sequential()
Dim Range_Value As Integer, i As Integer, Last_Added_Row As Long, Last_AddedDate As Long
' Check if A2 has a date
If IsEmpty(ActiveSheet.Range("A2").Value) = True Then
MsgBox "Please Enter the Date in A2 Cell"
Else
' Get the number of days to extend
Range_Value = ActiveSheet.Range("C2").Value
' Find the last row in column A with a date
Last_Added_Row = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
' Get the date from the last row
Last_AddedDate =
ActiveSheet.Range("A" & Last_Added_Row).Value
' Loop to add the
sequential dates
For i = 1 To Range_Value
ActiveSheet.Range("A" &Last_Added_Row + i) = DateAdd("d", i, Last_AddedDate)
Next i
End If
End Sub
- Click on Generate Date Sequence, it will the check Range value and Input Date parameter and based on these two value 5 more rows will added below Cell A2.
Insert ActiveX Buttons in Excel Sheet:
- Go to the Developer tab, click Insert, and choose Command Button (ActiveX Control).
Switch to Design Mode to adjust the button properties, such as caption and size.
- Click View Code to write the VBA code for the button’s click event, for example:
Private Sub CommandButton1_Click()Dim Range_Value As Integer, i As Integer, Last_Added_Row As Long, Last_AddedDate As Long
' Check if A2 has a date
If IsEmpty(ActiveSheet.Range("A2").Value) = True Then
MsgBox "Please Enter the Date in A2 Cell"
Else
' Get the number of days to extend
Range_Value = ActiveSheet.Range("C2").Value
' Find the last row in column A with a date
Last_Added_Row = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
' Get the date from the last row
Last_AddedDate = ActiveSheet.Range("A" & Last_Added_Row).Value
' Loop to add the sequential dates
For i = 1 To Range_Value
ActiveSheet.Range("A" & Last_Added_Row + i) = DateAdd("d", i, Last_AddedDate)
Next i
End If
End Sub
- You can write the below code for Sub CommandButton1_Click().
- Exit Design Mode to use the button..
- Click On Generate Date Sequence.
Insert Shape Button in Excel:
- Go to the Insert tab and select a shape.
- Drag the shape onto your Excel sheet.
- You can also customize the shape’s appearance by formatting it as needed.
- Right-click the shape and choose Assign Macro.
- Select the macro and click OK.
Sub Add_Date_Sequential()
Dim Range_Value As Integer, i As Integer, Last_Added_Row As Long, Last_AddedDate As Long
' Check if A2 has a date
If IsEmpty(ActiveSheet.Range("A2").Value) = True Then
MsgBox "Please Enter the Date in A2 Cell"
Else
' Get the number of days to extend
Range_Value = ActiveSheet.Range("C2").Value
' Find the last row in column A with a date
Last_Added_Row = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
' Get the date from the last row
Last_AddedDate = ActiveSheet.Range("A" & Last_Added_Row).Value
' Loop to add the sequential dates
For i = 1 To Range_Value
ActiveSheet.Range("A" & Last_Added_Row + i) = DateAdd("d", i, Last_AddedDate)
Next i
End If
End Sub
- Click on Generate Date Sequence.
Insert Macro Button in Quick Access Ribbon:
- Right-click on the Excel Ribbon and select Customize the Ribbon.
- Under Choose commands from, select Macros.
- It will show all the available Macros in Excel. Select the macro you want to add and click Add.
- You can create a New Tab and Group to add a macro under excel ribbon for easy access.
- Create a New Tab and you can rename with proper icon.
- Click on Add to Insert macro into New Tab
- Click Ok. A New tab will be visible under Excel Ribbon.
| Feature | ActiveX Button |
|
Shape Button | |
|---|---|---|---|---|
| Macro Assignment |
Directly assign macros through VBA editor | Assign macros through the “Assign Macro” dialog | Assign macros through the “Assign Macro” dialog | |
| Event Handling | Supports multiple events (e.g., click, double-click) | Limited to click event | Limited to click event | |
| Ease of Use | Requires knowledge of VBA for advanced use | Simple to assign and use | Simple to assign and use | |
| Customization | Highly customizable with extensive properties | Basic customization options | Customizable appearance, limited functionality | |
| Performance | May affect performance if overused | Generally better performance | Minimal impact on performance | |
| Compatibility | May have compatibility issues on different systems | Highly compatible across different systems | Highly compatible across different systems |
Comments
Post a Comment