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

[sebastienm]

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

[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

>-----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,
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
>>
>.
>

[sebastienm]

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!
>-----Original Message-----
>Mike,
>Use
> Application.CommandBars(1)...
>instead of
> CommandBars(1)...
>(2 instances in CreateMenu and 1 in DeleteMenu)
>
>Regards,
>Sebastien
>.
>