How To Pass arguments to macros from buttons and menus using VBA in Excel?

#topassargumentstomac #vbamacros

In this article, you will learn how to pass arguments to macros from buttons and menus using VBA ** in Microsoft Excel. Let’s see them below!! Get the official version of **MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Example

Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
Then, you need to ** ** select the Visual Basic option under the Code section.

Now, you have to copy and paste the code given below.

Sub AddCommandToCellShortcutMenu()
Dim i As Integer, ctrl As CommandBarButton
    DeleteAllCustomControls ' delete the controls if they already exists
    ' create the new controls
    With Application.CommandBars(25) ' the cell shortcut menu
        ' add an ordinary commandbarbutton
        Set ctrl = .Controls.Add(msoControlButton, , , , True)
        With ctrl
            .BeginGroup = True
            .Caption = "New Menu1"
            .FaceId = 71
            .State = msoButtonUp
            .Style = msoButtonIconAndCaption
            .Tag = "TESTTAG1"
            .OnAction = "MyMacroName2"
        End With
        ' add a button that passes one string argument
        Set ctrl = .Controls.Add(msoControlButton, , , , True)
        With ctrl
            .BeginGroup = False
            .Caption = "New Menu2"
            .FaceId = 72
            .Style = msoButtonIconAndCaption
            .Tag = "TESTTAG2"
            .OnAction = "'MyMacroName2 ""New Menu2""'"
        End With
        ' add a button that passes passes one string argument
        Set ctrl = .Controls.Add(msoControlButton, , , , True)
        With ctrl
            .BeginGroup = False
            .Caption = "New Menu3"
            .FaceId = 73
            .Style = msoButtonIconAndCaption
            .Tag = "TESTTAG3"
            .OnAction = "'MyMacroName2 """ & .Caption & """'"
        End With
        ' add a button that passes two arguments, a string and an integer
        Set ctrl = .Controls.Add(msoControlButton, , , , True)
        With ctrl
            .BeginGroup = False
            .Caption = "New Menu4"
            .FaceId = 74
            .Style = msoButtonIconAndCaption
            .Tag = "TESTTAG4"
            .OnAction = "'MyMacroName3 """ & .Caption & """, 10'"
        End With
    End With
    Set ctrl = Nothing
End Sub

Sub DeleteAllCustomControls()
' delete the controls if they already exists
Dim i As Integer
    For i = 1 To 4
        DeleteCustomCommandBarControl "TESTTAG" & i
    Next i
End Sub

Private Sub DeleteCustomCommandBarControl(CustomControlTag As String)
' deletes ALL CommandBar controls with Tag = CustomControlTag
    On Error Resume Next
    Do
        Application.CommandBars.FindControl(, , CustomControlTag, False).Delete
    Loop Until Application.CommandBars.FindControl(, , _
        CustomControlTag, False) Is Nothing
    On Error GoTo 0
End Sub

' example macros used by the commandbar buttons
Sub MyMacroName1()
    MsgBox "The time is " & Format(Time, "hh:mm:ss")
End Sub

Sub MyMacroName2(Optional MsgBoxCaption As String = "UNKNOWN")
    MsgBox "The time is " & Format(Time, "hh:mm:ss"), , _
        "This macro was started from " & MsgBoxCaption
End Sub

Sub MyMacroName3(MsgBoxCaption As String, DisplayValue As Integer)
    MsgBox "The time is " & Format(Time, "hh:mm:ss"), , _
        MsgBoxCaption & " " & DisplayValue
End Sub

You need to save the code by selecting it and then close the window.

Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
Then, you need to choose the Macros option in the Code section.

Now, you have to make sure that your macro name is selected and click the *Run * button.

Finally, after running the macro, you will get the output ** as **pass arguments to macros from buttons and menus in Excel.

Wrap-Up

From this article, you can get some clarification about how to pass arguments to macros from buttons and menus using VBA in Microsoft Excel. Let me know if you have any doubts regarding this article or any other Excel/VBA topic.

Thank you so much for visiting Geek Excel!! *If you want to learn more helpful formulas, check out Excel Formulas *!!

Keep Reading: