1

I'm trying to use an addin which works fine on PC as well as on Excel 2011 for Mac. When I try to use it with Excel 2019 on mac I get the error message above when trying to add controls. The last line in the following code is causing the problem:

Sub AddMenu()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl
'Delete any existing one. We must use On Error Resume next in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&AS Tool").Delete
On Error GoTo 0
'Set a CommandBar variable to Worksheet menu bar
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
'Return the Index number of the Help menu. We can then use this to place a custom menu before.
iHelpMenu = cbMainMenuBar.Controls("Help").Index
'Add a Control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCustomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)
'Give the control a caption
cbcCustomMenu.Caption = "&AS Tool"
'Working with our new Control, add a sub control and give it a Caption and tell it which macro to run (OnAction).
With cbcCustomMenu.Controls.Add(msoControlButton)

Apparently, msoControlPopup is no longer supporting .controls.add. Is there a known workaround?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Mike
  • 40
  • 6
  • I did read somewhere that Microsoft were going to kill off the CommandBars collection in the next version of Office. It looks like you'll have to move onto Ribbons? – NeepNeepNeep Oct 30 '18 at 11:13
  • I would love to, but information is very sparse (even on MS sites). Are there any resources available explaining how to convert existing code? – Mike Oct 30 '18 at 13:47
  • Sorry, I'm not really sure. The closest I got was modifying the ribbon in Excel 2010/13/16 which all still allowed manipulating the CommandBars collection. – NeepNeepNeep Oct 30 '18 at 16:19

0 Answers0