Weird Menu Behaviour in 2013 - apparently?

[nigelh]

Can someone please advise me what I need to change to overcome what appears to be some weird behaviour with custom menus in 2013? The situation is as follows.

I have an application comprising two "control" workbooks, an Add-in, and then many other workbooks. The user opens the first control workbook, and this causes the Add-in to be loaded. Once the Add-in is loaded, the second control workbook is opened (it remains the active workbook), and then the custom menu is then created and added to the menu bar (annoyingly behind the ADDIN button on the ribbon, but I've got over that!) The user now has access to the application, and using buttons on the custom menu (and its sub-menus) can open and process other workbooks.

The application was originally written in 1997 and so has been through various Excel versions (2003, 2010) with no changes - working happily in each. I now have to make sure the application runs correctly in 2013 - and, by and large, it does. However, I have noted a couple of very annoying things to do with my custom menu.

1. When the application goes through the start up process, the ADDIN menu button, only appears on the second control workbook: there is no custom menu available on the first one. I do not want to add the menu until the second workbook is opened in case there are some processing issues. Do I have to activate the first control workbook before adding the custom menu? If so, what does this do for the second workbook which is now already open.

2. During development testing, I occasionally have to re-define the menu. In 2010 and previous versions, I could do this by hitting ALT + F8 and running a macro to reset the menu back to the "out of the box" menu, and then re-defining the custom menu to get the new version. However, when I do this from one of the user workbooks, I find that the new menu is created in the workbook that was active when I pressed ALT + F8, but the custom menu has not been updated on any of the other workbooks that were open at the time.

It almost seems as though the custom menu is no longer "application-wide" but each workbook has its own version that is copied from somewhere, but then behaves independently.

Am I missing something in the definition of the menu bar that has changed from 2010 to 2013?

Any help gratefully received
Nigel H

 

[starl]

In 2007, Excel wasn't SDI (Single Document Interface). Now, it is. You'll have to change your menu logic. Read up on it here:

https://msdn.microsoft.com/en-us/library/office/dn251093.aspx

 

[jmholdermn]

I am also having issues creating menus through my addin. In 2010 they worked fine but when I upgraded to 2013 I get the following error:

Run-time error '9':
Subscript out of range

When I open Debug, it points to the following line as the offending code block:

Set MenuObject = Application.CommandBars(1). _
                                Controls.Add(Type:=msoControlPopup, _
                                Before:=PositionOrMacro, _
                                Temporary:=True)

Here is the entire menu creation code I'm using:

Sub CMR_Menu_Init()


    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("CMR_Menu")  'Location for menu data

    Call CMR_Menu_Delete  'Make sure the menus aren't duplicated

    Row = 2 'Initialize the row counter
    
    Do Until IsEmpty(MenuSheet.Cells(Row, 1)) 'Add the menus, menu items and submenu items using data stored on MenuSheet
        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 ' Menu Header
                    ' In Excel 2003 and earlier, this will create a commandbar or a dropdown at the top of the program
                    ' In Excel 2007 and 2010 it creates a menu listing only accessible by clicking
                    ' on the "Add In" menu item
                Set MenuObject = Application.CommandBars(1). _
                                Controls.Add(Type:=msoControlPopup, _
                                Before:=PositionOrMacro, _
                                Temporary:=True)
                    MenuObject.Caption = Caption

               Case 2 ' Top Tier Menu, fisrt usable section
                    ' I use this section to separate like items
                    'Each entry in this level contains a submenu or is a standalone 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
                    End If
                    If Divider Then
                        MenuItem.BeginGroup = True
                    End If
            Case 3 ' SubMenu level
                Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                    SubMenuItem.Caption = Caption
                    SubMenuItem.OnAction = PositionOrMacro
                If FaceID <> "" Then
                    SubMenuItem.FaceID = FaceID
                End If
                If Divider Then
                    SubMenuItem.BeginGroup = True
                End If
             End Select
        Row = Row + 1
    Loop
    
'#################  CMR_Menu_Init  #####################
End Sub

Sub CMR_Menu_Delete()
'   Executed when the workbook is closed to delete the menu so other workbooks are not effected
'   Deletes the Menus
    Dim MenuSheet As Worksheet
    Dim Row As Integer
    Dim Caption As String
On Error Resume Next
    Set MenuSheet = ThisWorkbook.Sheets("CMR_Menu")
    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

'#################  CMR_Menu_Delete  #####################
End Sub

I've tried to find the answer using the link provided in the previous reply but I must not be understanding the issue.

Am I doing something incorrectly, this exact code worked in 2007 and 2010.



John