Creating a toolbar for my add-in

[Mike]

Thanks in advance for any help.

I have a macro that I use a lot that I want to turn into
an add-in. I have saved it as an XLA file and selected it
through the Tools -> Add-Ins Menu. However the menu
button for my add-in does not appear after installing the
add-in.

I created the menu button with the following code at the
beginning of my macro:

Dim HelpMenu As CommandBarControl
Dim NewMenu As CommandBarPopup
Dim MenuItem As CommandBarControl
Dim Submenuitem As CommandBarButton

On Error Resume Next
CommandBars(1).Controls("AP").Delete
On Error Resume Next

Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
If HelpMenu Is Nothing Then
Set NewMenu = CommandBars(1).Controls _
.Add(Type:=msoControlPopup, temporary:=True)
Else
Set NewMenu = CommandBars(1).Controls _
.Add(Type:=msoControlPopup, Before:=HelpMenu.Index,
temporary:=True)
End If

NewMenu.Caption = "&AP"

Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
With MenuItem
.Caption = "F&oundations"
.OnAction = "ShowDialog"
End With

The rest of my code follows.

Does anyone know why this button does not appear when I
install the add-in?

Thanks again for any help. I am using Excel 2000.

Mike

 

[Guest]

Hi,
Where have you put the code? It should be in the Workbook_Open of the
ThisWorkbook module. I would think you code currently doens't excute... Put a
msgbox "Create menu" at the begining of the sub.
Maybe you are using the AddIn Install event sub. This sub only executes once
when the book is installed as addin (same as when click the AddIn in the Tool

Addin menu), not every time it opens. Similar with Uninstall.

Regards,
Sebastien

 

[Mike]

Thanks for your help Sebastien,

I added the code below to the Workbook_Open of the
ThisWorkbook Module. Now I get Run-time error 91: Object
variable or With block variable not set. Any advice?

Thanks again ...Mike

Option Explicit
Private Sub Workbook_Open()
CreateMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteMenu
End Sub

Sub CreateMenu()

Dim HelpMenu As CommandBarControl
Dim NewMenu As CommandBarPopup
Dim MenuItem As CommandBarControl
Dim Submenuitem As CommandBarButton

Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
If HelpMenu Is Nothing Then
Set NewMenu = CommandBars(1).Controls _
.Add(Type:=msoControlPopup, temporary:=True)
Else
Set NewMenu = CommandBars(1).Controls _
.Add(Type:=msoControlPopup, Before:=HelpMenu.Index,
temporary:=True)
End If

NewMenu.Caption = "&AP"

Set MenuItem = NewMenu.Controls.Add
(Type:=msoControlButton)
With MenuItem
.Caption = "F&oundations"
.OnAction = "ShowDialog"
End With

End Sub

Sub DeleteMenu()
On Error Resume Next
CommandBars(1).Controls("AP").Delete
On Error Resume Next
End Sub

 

[Guest]

Mike,
Use
Application.CommandBars(1)...
instead of
CommandBars(1)...
(2 instances in CreateMenu and 1 in DeleteMenu)

Regards,
Sebastien

 

[Mike]

That does the trick. Thank you very much Sebastien.
You're the best!