Creating a toolbar for my add-in
 

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, Befo=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

Creating a toolbar for my add-in
 

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" wrote:

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, Befo=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

Creating a toolbar for my add-in
 

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, Befo=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

-----Original Message-----
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" wrote:

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,
Befo=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

.

Creating a toolbar for my add-in
 

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

Regards,
Sebastien

Creating a toolbar for my add-in
 

That does the trick. Thank you very much Sebastien.
You're the best!
-----Original Message-----
Mike,
Use
Application.CommandBars(1)...
instead of
CommandBars(1)...
(2 instances in CreateMenu and 1 in DeleteMenu)

Regards,
Sebastien
.