-1

I have a fully functional Macro that I'm trying to convert to an Add-In so I can use it on all excel documents. However I can't get it create a menu. Please Help.

First Things First:

I'm using Office 2011-Excel on a Mac running Mountain Lion

My .xlam file is saved in the correct folder Applications/Microsoft Office 2011/Office/Add-Ins

I've installed it correctly using the Tools/Add-Ins menu

My code is in the This Worksheet section of the .xlam file

My Add-In's source code is viewable from all documents

My code as follows:

Option Explicit

Dim cControl As CommandBarButton

Private Sub Workbook_Open()

On Error Resume Next 'Just in case

    Application.CommandBars("Worksheet Menu Bar").Controls("P Wave").Delete 'Delete any existing menu item that may have been left.
    Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add 'Add the new menu item and Set a CommandBarButton Variable to it

        With cControl 'Work with the Variable
            .Caption = "P Wave"
            .Style = msoButtonCaption
            .OnAction = "runSheet()"
            'Macro stored in a Standard Module
        End With
    On Error GoTo 0
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next 'In case it has already gone.

    Application.CommandBars("Worksheet Menu Bar").Controls("P Wave").Delete

    On Error GoTo 0

End Sub

I can't get my Macro menu to appear. Please help

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Jamie Marshall
  • 1,885
  • 3
  • 27
  • 50
  • The sad reason is that you can't tweak command bars with VBA in Excel 2011. This is one of the many compatibility problems with Excel 2011. – Netloh Oct 15 '14 at 21:41
  • ****(swearing), what would be a good work around then. I need some sort of easily accessible way to execute my Sub on command. I need something that is compatible with as many versions of Excel as possible and can be easily handled by a neanderthal. – Jamie Marshall Oct 15 '14 at 21:49
  • I don't have any experience with it my self, but here is an example of method that may be suitable to you: http://www.rondebruin.nl/mac/addins/macvbamenu.htm – Netloh Oct 15 '14 at 22:42

1 Answers1

0

Add your command buttons, or even your own Tab, on the Ribbon although you may have to learn a bit about RibbonX XML. It will give compatibility back to Excel 2007 (I think).

This link may get you started.

Also, This visual designer may be more exciting to get you going. I haven't used it but Andy Pope is the man on all things VBA!

barryleajo
  • 1,956
  • 2
  • 12
  • 13
  • This question is regarding Excel 2011 for Mac. It is not possible to edit the ribbon by XML in this version. – Netloh Oct 15 '14 at 22:43
  • OK thank you - missed that. You could check out VMWare. – barryleajo Oct 15 '14 at 23:03
  • Hold the press, I can defiantly create a menu on the command bar using VBA by running a macro. Its just when I turn my "Macro" into an "Add-Inn" that the menu creation fails. Just checking to make sure that's what everyones saying. Editing the ribbon is also not working for an "Add-In" – Jamie Marshall Oct 15 '14 at 23:20
  • I just tested it some more. It looks like the only thing not working is `Private Sub Workbook_Open()` and `Private Sub Workbook_BeforeClose()` Also `Option Explicit` seems to deactivate the code all together – Jamie Marshall Oct 15 '14 at 23:42
  • You probably need to check out the VBA object model for the Mac, what's in, what's out etc. Try this link to get started perhaps: http://stackoverflow.com/questions/5071171/where-can-i-find-the-mac-office-2011-developer-details OR THIS which deals with the Ribbon specifically http://office.microsoft.com/en-gb/mac-word-help/customize-the-ribbon-HA102927321.aspx?CTT=5&origin=HA103526326 – barryleajo Oct 16 '14 at 06:44
  • Thanks, but I took a look at all of the object changes on office 2010 (which according to those references should be the same as 2011), nothing that would cause Workbook_Open() to fail. I'm going to move this under a new question to focus solely on why Workbook_Open() would fail. I think this will better reflect the scope of this question: http://stackoverflow.com/questions/26408113/workbook-open-wont-execute-excel-2011 – Jamie Marshall Oct 16 '14 at 15:23