Help with creating menu item for Addin

[BAlGaInTl]

I know that this has been covered before, and I have a working macro that creates a menu item in order to run a macro. Here is the code that I'm currently using...

Private Sub CreateMenu()
    Dim NewMenu As CommandBarPopup
            Set NewMenu = CommandBars("Worksheet Menu Bar").Controls.Add _
                (Type:=msoControlPopup)
    With NewMenu
        .Caption = "QAD"
        .OnAction = "ShowQADForm"
    End With
End Sub

I want this macro to automatically run when the Addin is installed. However, when I create this same macro under Workbook_AddinInstall, I get an error.

Run-time error '91':
     Object variable or with block variable not set

The Set NewMenu line is highlighted when I debug. What do I need to change to get it to work automatically?

Also, I would like to create the item before the 'Help' menu item, but all of the Before:= statements that I have tried up to this point have failed. That is by no means a show stopper, it would just be nice.

Any help is appreciated.

TIA

 

[Andy Pope]

Hi sdible,

You need to add the Application object to your CommandBars.
The intIndex variable stores the position of the Help menu item.

    Dim NewMenu As CommandBarPopup
    Dim intIndex As Integer
    
    intIndex = Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=30010).Index
    Set NewMenu = Application.CommandBars("Worksheet Menu Bar").Controls.Add _
                (Type:=msoControlPopup, Before:=intIndex)
    With NewMenu
        .Caption = "QAD"
        .OnAction = "ShowQADForm"
    End With

Cheers
Andy

 

[BAlGaInTl]

Thanks so much... that works like a charm..

However... (of course that was coming)

I thought I could figure out how to set up the AddinUninstall code in order to remove the custom menu when the Addin in Uninstalled.

I can do it by the index number used on my machine, but I think it will be different on others.

How can I set up the intIndex = command to find my new custom "QAD" menu so I can delete it?

TIA

 

[Andy Pope]

This should do it.

Application.CommandBars("Worksheet Menu Bar").Controls("QAD").Delete

Cheers
Andy

 

[BAlGaInTl]

I tried that...

I don't think it worked, but I will try again.

Also...

Hopefully you can help me... in playing around with my stuff, I inadvertently deleted the Help menu.

Is there a quick way to get it back?

[edit]
It seems to work now... I must have had a typo in there before. I hate stupid mistakes.

I also managed to add the Help menu back in.
[/edit]

Everything works like a dream now. Thanks for your help.