UtterAccess Discussion Forums > Add-in Macro Control Argument

Full Version: Add-in Macro Control Argument

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

doctor9

Feb 23 2012, 02:17 PM

Years ago I created an Excel Add-In that contains roughly a dozen macros that several of my users like to have handy at any time. It was first developed in Excel 2003, and included both a custom toolbar and a custom menu. The menu was created by using two columns of a worksheet named "ListOfMacros" in the Add-In; Column A had the macro's actual VBA name. Column B had the informal name that appeared on the menu. Here's my code for creating the custom menu:

CODE

Option Explicit

'   Code heavily based on techniques from "Excel VBA Macro Programming" by
'   Richard Shepherd (ISBN 0-07-223144-0)

Private Const C_TAG = "MTEAddIn"
Private Const C_TOOLS_MENU_ID As Long = 30007&

Private Sub Workbook_BeforeClose(Cancel As Boolean)

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Workbook_BeforeClose
' Before closing the add-in, clean up our controls.
''''''''''''''''''''''''''''''''''''''''''''''''''''
    DeleteControls

End Sub

Private Sub Workbook_Open()

'''''''''''''''''''''''''''''''''''''''''''''''
' Workbook_Open
' Create a submenu on the Tools menu. The
' submenu has several controls on it.
'''''''''''''''''''''''''''''''''''''''''''''''
    Dim ToolsMenu As Office.CommandBarControl
    Dim ToolsMenuItem As Office.CommandBarControl
    Dim ToolsMenuControl As Office.CommandBarControl

    Dim intNumItems As Integer, strButtonName(20) As String
    Dim strMenuName(20) As String, strMacroName(20) As String
    Dim i As Integer

'   Loop through the rows of text on Worksheet "ListOfMacros" to assign the
'   macro names/menu names/button names
    intNumItems = 0
    While Sheets("ListOfMacros").Cells(intNumItems + 2, 1) <> ""
        intNumItems = intNumItems + 1
        strMacroName(intNumItems) = Sheets("ListOfMacros").Cells(intNumItems + 1, 1)
        strMenuName(intNumItems) = Sheets("ListOfMacros").Cells(intNumItems + 1, 2)
        strButtonName(intNumItems) = Sheets("ListOfMacros").Cells(intNumItems + 1, 3)
    Wend

'''''''''''''''''''''''''''''''''''''''''''''''
' First delete any of our controls that
' may not have been properly deleted previously.
'''''''''''''''''''''''''''''''''''''''''''''''
    DeleteControls

''''''''''''''''''''''''''''''''''''''''''''''
' Get a reference to the Tools menu.
''''''''''''''''''''''''''''''''''''''''''''''
    Set ToolsMenu = Application.CommandBars.FindControl(ID:=C_TOOLS_MENU_ID)
    If ToolsMenu Is Nothing Then
        MsgBox "Unable to access Tools menu.", vbOKOnly
        Exit Sub
    End If

''''''''''''''''''''''''''''''''''''''''''''''
' Create a new submenu on the Tools menu.
''''''''''''''''''''''''''''''''''''''''''''''
    Set ToolsMenuItem = ToolsMenu.Controls.Add(Type:=msoControlPopup, temporary:=True)
    If ToolsMenuItem Is Nothing Then
        MsgBox "Unable to add item to the Tools menu.", vbOKOnly
        Exit Sub
    End If

    With ToolsMenuItem
        .Caption = "MTE Utils"
        .BeginGroup = True
        .Tag = C_TAG
    End With

''''''''''''''''''''''''''''''''''''''''''''''
' Create the macro controls as new items
' in the Tools->MTE Utils menu.
''''''''''''''''''''''''''''''''''''''''''''''
    For i = 1 To intNumItems
        Set ToolsMenuControl = ToolsMenuItem.Controls.Add(Type:=msoControlButton, temporary:=True)
        If ToolsMenuControl Is Nothing Then
            MsgBox "Unable to add item to Tools menu item.", vbOKOnly
            Exit Sub
        End If

        With ToolsMenuControl
''''''''''''''''''''''''''''''''''''
' Set the display caption and the
' procedure to run when clicked.
''''''''''''''''''''''''''''''''''''
            .Caption = strMenuName(i)
            .OnAction = "'" & ThisWorkbook.Name & "'!" & strMacroName(i)
            .Tag = C_TAG
        End With
    Next i

End Sub

Private Sub DeleteControls()

''''''''''''''''''''''''''''''''''''
' Delete controls whose Tag is
' equal to C_TAG.
''''''''''''''''''''''''''''''''''''
    Dim Ctrl As Office.CommandBarControl

    On Error Resume Next
    Set Ctrl = Application.CommandBars.FindControl(Tag:=C_TAG)

    Do Until Ctrl Is Nothing
        Ctrl.Delete
        Set Ctrl = Application.CommandBars.FindControl(Tag:=C_TAG)
    Loop

End Sub

Now, this code used to include extra lines for assigning the macros to the custom toolbar as well. I've since stripped that code out, since toolbars are no longer supported in Excel 2010.

The good news is that I've been able to create a custom Ribbon to replace the custom Toolbar. The bad news is that by switching from a Toolbar to a Ribbon, I've been required to add an argument to each macro.

One of my Macro headers, in the old format:
Sub ConvertDirOfTextFiles()

The new format, to make the macro work by being triggered from the custom Ribbon:
Sub ConvertDirOfTextFiles(control As IRibbonControl)

This is great for making the Ribbon work; I didn't have to alter my macros in any other way. However, I've still got a nice menu (which now appears in the Add-Ins tab of the ribbon) that no longer works because of this change. As things stand right now, if I try to select any menu item, I get this error:

Argument not optional

So, my question is this; can I alter my menu-creating VBA above in such a way that I can make the menu send some sort of control object as an argument, thus allowing the macro to run from either the Ribbon or the menu?

Any ideas?

Dennis

doctor9

Mar 26 2012, 10:57 AM

I believe I may have a solution - I haven't tried it yet, but the theory should be sound...

I shall create a code module that consists of a series of little subroutines along these lines:

CODE

Sub ConvertDirOfTextFiles(control As IRibbonControl)

Call ConvertDirOfTextFiles1

End Sub

These will be the subroutines that are called by the ribbon controls. The actual macros that do the work will be CALLED from within these subroutines.

On the other hand, the custom menu will call the actual macros directly.

Hopefully someone will find this useful in the future...

Dennis

doctor9

May 10 2012, 04:40 PM

...And I'm back. I found a RIDICULOUSLY simple solution that requires very little extra code.

Make any macro called from the Ribbon look like this:

CODE

Sub MacroName(Optional Control As IRibbonControl)

Simply by adding the "Optional" bit, the macro can now be called from the custom menu OR the Ribbon.

Hope this helps SOMEONE out there.

Dennis

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.