Pass arguments to macros from buttons and menus

The example below shows how you can create CommandBar buttons/menus that passes one or more arguments to a macro. The example also shows how you can add a new item to the Cell shortcut menu.

Sub AddCommandToCellShortcutMenu()
Dim i As Integer, ctrl As CommandBarButton
    DeleteAllCustomControls ' delete the controls if they already exists
    ' create the new controls
    With Application.CommandBars("Cell") ' 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 = "MyMacroName1"
        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

Document last updated 2000-02-05 12:47:00 Printerfriendly version

User comments:

ola from Ilorin, Nigeria wrote (2006-10-20 00:17:12 CET):

Re: Displaying a modeless UserForm
Thanks for your help and time. Some how this is not working for me, when I try this via VBA its ok in both Execl and Word but when I use COM it doest work, perhaps I am missing something.

Ola

Ole P. from Norway wrote (2006-10-18 23:03:21 CET):

Re: Displaying a modeless UserForm
I can't reproduce the error you describe on my computer.
Using the argument vbModeless (or 0) will display a modeless userform on my computer:

Sub ShowUserForm()
Load UserForm1
UserForm1.Show vbModeless
End Sub

Sub CloseUserForm()
UserForm1.Hide
Unload UserForm1
End Sub

Ola from Ilorin, Nigeria wrote (2006-10-18 03:58:15 CET):

Re: What of changing Caption font styles?
Thanks for the responce.

I have considered this it would have been ideal, but I cant seem to get the com addin form to stay modeless I am using the following code

userform1.show 0

Thanks for any help in advance
Ola

Ole P. from Norway wrote (2006-10-17 09:33:19 CET):

Re: What of changing Caption font styles?
If you need custom font styles you will have to create your own custom dialogs using UserForms.

ola from Ilorin, Nigeria wrote (2006-10-17 02:24:04 CET):

What of changing Caption font styles?
This really works for me but is there a way to change the font style to some other rather than the windows default?

Alice Coppola from Aberdeen, NJ - USA wrote (2006-07-07 22:06:18 CET):

Creating Toolbars & Menu Buttons in VBA
Thank you! After much searching on Internet (MS was little help), I created a Custom Toolbar with Buttons that are not images, but Text. This Toolbar creates when you lauch the Workbook and deletes when you close the workbook.

Andy from Detroit, MI wrote (2006-01-28 07:28:17 CET):

Re: Here is one scenario where it doesn't work
Thanks for setting me in the right direction.

Now I have yet another issue. My sheet are normally set to Page Break Preview and "Cell" does not work in that view. When I toggle view to Normal, then "Cell" works.

Seems like this might be key to the problems experienced by others, it all depends on what type of cell is being right-clicked.

Another funny thing is that "Query Layout" works only in Page Break Preview", while "Query" only works in Normal View. This is all crazy. I did a loop to find the names of all popup commandbars -- 63.

Now, what I'll do tonight is iterate thru each commandbar.name and see which one will trigger the equivalent of "Cell" when in Page Break Preview. If anyone knows the answer to that, I will be most grateful, as it will save me from finding out the long way.

In any case it looks like my template will end up with 4 sets of commandbars, 2 each to work if the user is in Normal view, whether right-clicking in a query field or normal cell. And another 2 each to work when in Page Break Preview.

Given the number of commandbars that I need to have loaded and available, I will create all commandbars variations during 'Workbook Open' and set them all to 'Visible = False'. Then whenever something is true, have them 'Visible = True'.

TIA

Ole P. from Norway wrote (2006-01-27 13:21:53 CET):

Re: Here is one scenario where it doesn't work
You might think that it doesn't work...
When you right-click in a normal cell range, the "Cell" shortcut meny is displayed.
When you right-click in a cell range containing a query result (like the one you get after using the data import feature in Excel), the "Query" shortcut meny is displayed.
If you add your custom menu items to the "Query" commandbar instead of the "Cell" commandbar you will get the functionality you are looking for.

Andy from Detroit, MI wrote (2006-01-27 06:21:23 CET):

Here is one scenario where it doesn't work
Hi again,

this drove me nuts at first, then I realized that your example works fine everywhere else but within a certain range due to a conflict with SQL fields, the type that is created to get data from a TXT file; as in "(ActiveSheet.QueryTables.Add(..." --- from your "Import data from a text file (ADO) - Erlandsen Data Consulting" page.

Whenever I right-click one of the cells within the Query range, the context menu changes to include 4 new items: "Edit Query...", "Data Range Properties...", "Parameters..." & "Refresh Data..." But the context simply will not add my custom menu items.

However, when I delete the named range for a particular cell, then custom context menu works.

Unfortunately, this is exactly the place where I need the custom context menu to work. My concept is that after the general query is run, any cells that fails to get a valid return from my master TXT file, the user would be able to right-click the cell in question and choose from a couple of options to resolve the error.

I even tried using 'before', i.e. 5 & 6, (see next paragraph below) to force the position of my custom menu to come before the Query Menu. I imagine the fix may require first deleting the named ranges from the query field. But I'm hoping there may be a neater solution.

For what it is worth, I am using .CommandBars("Cell").Controls.Add(msoControlButton, , , 5, True)" and an IF statement to determine if the selected cell falls within a specified range ("E15:E5000").

Thanks in advance.

Andy

Ole P. from Norway wrote (2005-12-04 13:46:21 CET):

Re: Not working in Excel 2000
Don't know what you have done wrong, but this is certainly working in Excel 2000 and later versions on my computers...