Code not working with new version of Excel

scotts

New Member
Joined
Jun 26, 2010
Messages
42
Hi all,

Hopefully someone can help. I have the following code (part of a bigger piece of work) and until I started using 365 worked fine. Now I receive a runtime 13 error and I cant for the life of me figure it out other than it may be something to do with the adding a menu bar item or commandbar issue as it may have been demised but I dont know how to fix it!!

The code where the error is generated is shown below in red

All the tabs referenced are in place and triple checked

The full code is as follows:
Rich (BB code):
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


    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    '   Location for menu data
    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
    ''''''''''''''''''''''''''''''''''''''''''''''''''''


    '   Make sure the menus aren't duplicated
    Call DeleteMenu


    '   Initialize the row counter
    Row = 2


    '   Add the menus, menu items and submenu items using
    '   data stored on MenuSheet


    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
            '              Add the top-level menu to the Worksheet CommandBar
            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


    '   Adding the Presentation entry to the Menu
'    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
'    MenuItem.OnAction = "Presentation"
'    MenuItem.Caption = "Instant Report"
'    MenuItem.FaceId = 6980
    '   Adding the Question Maintenance entry to the Menu
    'Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
    '    MenuItem.OnAction = "Category_11"
    '    MenuItem.Caption = "&Questions"
    '    MenuItem.FaceId = 2985
'    ActiveWorkbook.Protect Password:=Wbklock
End Sub

Thanks in advance
 
Last edited by a moderator:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
The commandbar menu maker hasn't worked since the ribbon came into play, from what I can remember. You can use a UI Editor to create a ribbon item/menu/button(s), but that's an entirely different thing.
 
Upvote 0
Hi jproffer, thats what i feared. is there a viable workaround in VBA? Or is it simply not possible anymore? Is it a case of adding buttons to the workbook and running the code from there now????
 
Upvote 0
No workaround in VBA that I'm aware of. The ribbon editor (UI Editor) is totally separate from VBA and excel. It's not terribly difficult, it's just different. I'll try to put up an example tomorrow at work. Or it may be something you can find on google, not sure...never tried.
 
Upvote 0
Ok, here's a very simple version of the ribbon setup and call-backs...

The Setup: Made with the UI Editor found here.... (OK, for the life of me, I can't find the site to download the Custom UI Editor. Maybe someone else will come along with it, or start a new thread and ask that specific question)....anyhow, the setup:

HTML:
<customui xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>


<tab id="customTab" label="Shot Info 2.0" insertaftermso="TabPDF">
<group id="customGroup" label="Shot Tools">

            <button id="MRunSub1" label="Calculate Distance to Points in the Quarry" size="large" onaction="MyShotButton" imagemso="ArrowStyleGallery">
            </button><button id="MRunSub2" label="Calculate Great Circle Distance" size="large" onaction="MyShotButton" imagemso="ShowTimeZones">
            </button><button id="MRunSub3" label="Convert GPS Coordinates" size="large" onaction="MyShotButton" imagemso="RecurrenceEdit">
        </button><button id="MRunSub4" label="Convert Nautical/Statute Miles" size="large" onaction="MyShotButton" imagemso="HyperlinkInsert">
        </button><button id="MRunSub5" label="Area Calculator" size="large" onaction="MyShotButton" imagemso="BorderThickOutside">
            </button><button id="MRunSub6" label="Volume Calculator" size="large" onaction="MyShotButton" imagemso="Chart3DConeChart">
          </button><button id="MRunSub7" label="Weather Center" size="large" onaction="MyShotButton" imagemso="PictureBrightnessGallery">
          </button><button id="MRunSub8" label="Calculate Tons in Shot" size="large" onaction="MyShotButton" imagemso="ChartAreaChart">

and the Ribbon Callbacks which are contained in a regular code module within your project:

Code:
Sub MyShotButton(control As IRibbonControl)

    Select Case control.ID
        Case "MRunSub1"
            FrmDistance.Show
        Case "MRunSub2"
            FrmCircDist.Show
        Case "MRunSub3"
            FrmDMSConv.Show
        Case "MRunSub4"
            FrmStatToNaut.Show
        Case "MRunSub5"
            FrmAreaCalc.Show
        Case "MRunSub6"
            FrmVolCalc.Show
        Case "MRunSub7"
            FrmWeather.Show
        Case "MRunSub8"
            FrmShotTons.Show

    End Select
End Sub

As you can see, the OnAction matches the main subroutine name, and each button ID matches one of the cases to choose from. This is how it determines what to do when you click a certain button.</button></group></tab></tabs></ribbon></customui>
 
Last edited by a moderator:
Upvote 0
@jproffer
There is a test board where you can try things out, rather than cluttering up a thread.
Cheers

PS I've removed 2 of your failed attempts
 
Last edited:
Upvote 0
Sorry about that Fluff :) . While you're here.....any idea how to show text from the UI Editor without it taking it AS coding for the website??
 
Last edited:
Upvote 0
Thank you for that. While not quite a complete, it gives the general idea. :)

OP - Here is the entire text from the UI Editor for the above Ribbon Callbacks:

HTML:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>


<tab id="customTab" label="Shot Info 2.0" insertAfterMso="TabPDF">
<group id="customGroup" label="Shot Tools">
            <button id="MRunSub1" label="Calculate Distance to Points in the Quarry" size="large" onAction="MyShotButton" imageMso="ArrowStyleGallery" />
            <button id="MRunSub2" label="Calculate Great Circle Distance" size="large" onAction="MyShotButton" imageMso="ShowTimeZones" />
            <button id="MRunSub3" label="Convert GPS Coordinates" size="large" onAction="MyShotButton" imageMso="RecurrenceEdit" />
        <button id="MRunSub4" label="Convert Nautical/Statute Miles" size="large" onAction="MyShotButton" imageMso="HyperlinkInsert" />
        <button id="MRunSub5" label="Area Calculator" size="large" onAction="MyShotButton" imageMso="BorderThickOutside" />
            <button id="MRunSub6" label="Volume Calculator" size="large" onAction="MyShotButton" imageMso="Chart3DConeChart" />
          <button id="MRunSub7" label="Weather Center" size="large" onAction="MyShotButton" imageMso="PictureBrightnessGallery" />
          <button id="MRunSub8" label="Calculate Tons in Shot" size="large" onAction="MyShotButton" imageMso="ChartAreaChart" />
</group>
</tab>

</tabs>
</ribbon>
</customUI>

Thanks again Rory. :)
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top