VBA problem in Excel 2016

Question

I have a spreadsheet for analyzing language discourse structure.  It has a lot of VBA and has worked on Excel 2003, 2007, 2010, 2013 for Windows and Excel for Mac 2011.  However, now in Excel 2016, I get this error and none of the code runs.

Here is the section of code where the error occurs.  The bold line is the line highlighted in debug.  Any ideas?

Select Case MenuLevel

            Case 1 ' A Menu

'              Add the top-level menu to the Worksheet CommandBar

               If NextLevel = 2 Then

                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)

                   MenuItem.Caption = Caption

               Else

                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)

                    With MenuItem

                        .Caption = Caption

                        If actionchoice = 0 Then

                            .OnAction = "Entersometext(""" + texttoenter + """)"

                        Else

                            .OnAction = "AltEntertext(""" + texttoenter + """)"

                        End If

                    End With

                End If

            Case 2 ' A Menu Item

                Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)

                With SubMenuItem

                    .Caption = Caption

                    If actionchoice = 0 Then

                        .OnAction = "Entersometext(""" + texttoenter + """)"

                    Else

                        .OnAction = "AltEntertext(""" + texttoenter + """)"

                    End If

                End With

        End Select

Answer 1

That's because in Excel 2016 for the Mac, you are not allowed to customize or modify menu itmes. That facility is coming, but it will not support VBA modifications in the way you are trying to do it.

Answer 2

It seems that this move by MS is going to break a lot of stuff out there.

My code only adds popup menus (some with sub menus) that allow the insertion of user defined data in the current cell.  I don't see how that would be solved by ribbon customization.

The fact that the VBA editor seeems to be crippled doesn't help either.

Answer 3

Jim have you updated to 15.15.0 . Is it Possible  they have turned VBA partially, back on. The updates were very large taking 2 hours to download and install. And I use a Comcast "Blast

" Internet service at the time download was roughly 40 MBps.

One thing I did notice they did correct on the QAT the Printer, Icon was returned.  It was originally left off.

_________

Disclaimer:

The questions, discussions, opinions, replies & answers I create, are solely mine and mine alone, and do not reflect upon my position as a Community Moderator.

Answer 4

Following up on Bob's reply - Menus and toolbars are dead issues in Office 2016 for Mac. There's no support for them.

Microsoft has hinted heavily that Ribbon customization will be forthcoming very soon. I anticipate this will be via RibbonX to match Ribbon customization with Windows Office. Check out this page to learn about RibbonX. Be sure to set AutoUpdate.app to check for updates daily so you will be the first to be able to use this knowledge when the update is released.

http://www.rondebruin.nl/win/s2/win003.htm

Answer 5

I have a spreadsheet for analyzing language discourse structure. It has a lot of VBA and has worked on Excel 2003, 2007, 2010, 2013 for Windows and Excel for Mac 2011. However, now in Excel 2016, it throws this error:

Here is the section of code where the error occurs.  The bold line is the one highlighted in debug.  Any ideas on the problem?

Select Case MenuLevel

            Case 1 ' A Menu

'              Add the top-level menu to the Worksheet CommandBar

               If NextLevel = 2 Then

                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)

                   MenuItem.Caption = Caption

               Else

                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)

                    With MenuItem

                        .Caption = Caption

                        If actionchoice = 0 Then

                            .OnAction = "Entersometext(""" + texttoenter + """)"

                        Else

                            .OnAction = "AltEntertext(""" + texttoenter + """)"

                        End If

                    End With

                End If

            Case 2 ' A Menu Item

                Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)

                With SubMenuItem

                    .Caption = Caption

                    If actionchoice = 0 Then

                        .OnAction = "Entersometext(""" + texttoenter + """)"

                    Else

                        .OnAction = "AltEntertext(""" + texttoenter + """)"

                    End If

                End With

        End Select