UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Add-in Macro Control Argument, Office 2010    
 
   
doctor9
post Feb 23 2012, 02:17 PM
Post #1

UtterAccess Editor
Posts: 12,332
From: Wisconsin



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
#39;   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
Go to the top of the page
 
+
doctor9
post Mar 26 2012, 10:57 AM
Post #2

UtterAccess Editor
Posts: 12,332
From: Wisconsin



I believe I may have a solution - I haven't tried it yet, but the theory should be sound...
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
Go to the top of the page
 
+
doctor9
post May 10 2012, 04:40 PM
Post #3

UtterAccess Editor
Posts: 12,332
From: Wisconsin



...And I'm back. I found a RIDICULOUSLY simple solution that requires very little extra code.
Take 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
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 1st September 2014 - 03:59 PM