Consulting

Results 1 to 13 of 13

Thread: Addin menu creation

  1. #1
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location

    Addin menu creation

    Hi everybody!

    Got another question here. I'm working on an add-in, for my work this time. And I'm using J-Walks menu creator; I didn't want to develop one myself! Here: http://www.j-walk.com/ss/excel/tips/tip40.htm

    I can get everything to work OK, the structure is good, menu, icons (faceID's), macros run just fine from the addin. It just won't create itself when I load the addin anymore. I'm not really sure what to do with this as I haven't developed many addins. I'm calling the macro to create the menu on workbook_open, which calls the menu delete to prevent duplicates. If I run the sub manually, it works fine, but automatically is a different story.

    Anybody think they can help me out? Even a run through the basic steps to see if I missed something would be helpful! It's probably something really obvious, and I'm gonna feel really stupid about it. Thanks.

  2. #2
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Let's see what you got

  3. #3
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    In the .xls file, I've got:

    In ThisWorkbook module:

    Private Sub Workbook_Open()
    Call CreateMenu
    End Sub

    In a standard module (courtesy of J-Walk):

    Option Explicit
    Sub CreateMenu()
    '   This sub should be executed when the workbook is opened.
    '   NOTE: There is no error handling in this subroutine
        Dim MenuSheet As Worksheet
        Dim MenuObject As CommandBarPopup
        Dim MenuItem As Object
        Dim SubMenuItem As CommandBarButton
        Dim Row As Integer
        Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId
        Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
        Call DeleteMenu
        Row = 2
        Do Until IsEmpty(MenuSheet.Cells(Row, 1))
        With MenuSheet
            MenuLevel = .Cells(Row, 1)
            Caption = .Cells(Row, 2)
            PositionOrMacro = .Cells(Row, 3)
            Divider = .Cells(Row, 4)
            FaceId = .Cells(Row, 5)
            NextLevel = .Cells(Row + 1, 1)
         End With
         Select Case MenuLevel
             Case 1 ' A Menu
                Set MenuObject = Application.CommandBars(1). _
                Controls.Add(Type:=msoControlPopup, _
                Before:=PositionOrMacro, _
                Temporary:=True)
                MenuObject.Caption = Caption
           Case 2 ' A Menu Item
                If NextLevel = 3 Then
                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
                Else
                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
                    MenuItem.OnAction = PositionOrMacro
                End If
                MenuItem.Caption = Caption
                If FaceId <> "" Then MenuItem.FaceId = FaceId
                If Divider Then MenuItem.BeginGroup = True
            Case 3 ' A SubMenu Item
                Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = Caption
                SubMenuItem.OnAction = PositionOrMacro
                If FaceId <> "" Then SubMenuItem.FaceId = FaceId
                If Divider Then SubMenuItem.BeginGroup = True
        End Select
        Row = Row + 1
        Loop
    End Sub
    
    Sub DeleteMenu()
        Dim MenuSheet As Worksheet
        Dim Row As Integer
        Dim Caption As String
    On Error Resume Next
        Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
        Row = 2
        Do Until IsEmpty(MenuSheet.Cells(Row, 1))
        If MenuSheet.Cells(Row, 1) = 1 Then
            Caption = MenuSheet.Cells(Row, 2)
            Application.CommandBars(1).Controls(Caption).Delete
        End If
        Row = Row + 1
        Loop
        On Error GoTo 0
    End Sub
    I've got macros in other modules in this file. In the sheet set-up, I've got the menu layout/structure. I can upload a copy of the book if that'd help.

    I'm then saving the book as an .xla, adding it to my add-in list via the Browse button.

  4. #4
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    If you put error checking in the code above, and when the code is run as .xla, does thisworkbook contain MenuSheet? This may seem silly but the .xla's I have seen do not have a sheet embedded in them. Or do you create the sheet each time?

    ThisWorkbook.Sheets("MenuSheet")

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    You know, this thing is driving me nuts. I deleted the old add-in ver., saved the new ver. and all is fine now! Now it pops up every time! Oh well..

    And the add-in does show a ThisWorkbook module. Is that abnormal? I see it on every other add-in I've got also. And no, I don't create one every time.

    This has been an odd one. Thanks for your help/patience Tommy!

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    I've got another one for you though. Now that it loads up everytime, how would I delete the menu when taking off the add-in from the add-in list? I tried a workbook_close() event, but I don't think that's the key.

    Never done this before, so it's new territory for me here. Be gentle

  7. #7
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Is that abnormal?
    That's why I asked

    Let me look at it, on first glance it seems like the on unload event but I know that isn't right.

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    The files are uploaded. The .xls file is the one used to create the .xla. The add-in should self load ok. I put the workbook_close event on it, but doesn't seem to be working.

    And (ahem) ignore the HPP macro. It was my first macro I ever wrote. It's ugly

    Let me know what you think.
    Last edited by Zack Barresse; 06-17-2004 at 04:02 PM.

  9. #9
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    When the addin is unchecked from the add-in dialog box, it throws an uninstall event. There is a check you can make with the installed option it is true if it is installed and false otherwise.

    Umm not being picky but on this :PositionOrMacro = .Cells(Row, 3) the value in that cell is 14 I only have 10 (std) this may cause some trouble for you later, just an observation.

    Private Sub Workbook_AddinUninstall()
        Call DeleteMenu
    MsgBox "Gone"
    End Sub

  10. #10
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Good point Tommy! This is my workaround:

    1) After the Call DeleteMenu, type:

    Call posMacro
    2) Place this macro in the same module:

    Sub posMacro()
        Dim i As Integer
        i = Application.CommandBars(1).Controls.Count
        Cells(2, 3).Value = i + 1
    End Sub

    That took care of that Excellent point.

    And that code worked beautifully! I think it's about done! I can't thank you enough Tommy! THANK YOU!

  11. #11
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Hi Zack,

    My initial thought when I saw this was that you may be loading the addin with the fast load option /f in the registry settings. Not the case but I thought I'd add this to the thread in case it applies to someone else.

    Cheers

    Dave

    From
    http://msdn.microsoft.com/library/de...html/SFA96.asp

    Option Switches for the OPEN Entry
    You can use the /r and /f option switches, alone or together, to modify the behavior of the OPEN entry. The /r option switch opens the file as read-only. The /f (fast load) option switch places template documents in the New dialog box (accessed from the File menu). When used to open a macro sheet containing custom functions, the /f option switch places those functions in the Function Wizard dialog box. When you use one of the custom functions in a specified document, the macro sheet containing the function is opened automatically.

    For example, to open the file BUDGET.XLS as read-only every time Microsoft Excel is started, use the following value:

    /r c:\excel\budget92.xls

    To enter more than one OPEN entry, you must number them sequentially. For example, OPEN, OPEN1, OPEN2, and so on.

    If you have a macro sheet named CUSTOM.XLM that contains function macros, the following value will cause it to open automatically, and the functions it contains will be added to the User Defined function category in the Function Wizard dialog box:

    /f c:\excel\custom.xlm

    To use the /f switch with add-in macro sheets (.XLA), you must define the name __DemandLoad (the string "DemandLoad" preceded by two underscores) for the workbook. Microsoft Excel checks to see if the name is defined; the actual definition that you use is not important. You can define __DemandLoad as the Boolean value TRUE, for example.


    When an OPEN entry is used to open an add-in macro sheet on which the name __DemandLoad is defined, custom functions are displayed in the Function Wizard dialog box, but the add-in is not actually loaded until one of its functions is recalculated.

  12. #12
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Thanks Dave, that's a great piece of information!

    But no, I wasn't fast loading it. It's just a few useful macros I've whipped up and then some work related techie stuff for reporting.

    I had the macros on toolbar shortcuts, but felt they were taking up valuable *real estate* and decided to move them to a custom menu. I'm going to get to liking these custom menus They're pretty slick!

    Thanks for the input!!!

  13. #13
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Zack,
    Glad it works like you want it to. The fix you posted works like a charm also .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •