Working with Add-Ins and menus

S

Silas Mercer

I am trying to save my macros as an Add-In which, when loaded, wil
automatically create a menu in the worksheet menu bar complete wit
menu items which call macros in my add-in workbook (like man
commercially-available Add-Ins).

I have all my macros ready to go, but I'm not sure what code bes
accomplishes what I am trying to do. Do I want to use Workbook_Open (
and then call my create_menu macro or should I be looking at an Add-I
install event or something?

I just don't know how this is done, right now. I was thinking perhap
something like:

Sub Workbook_Open()
Call create_menu
End Sub

But this doesn't seem to work with an add-in. The key is, I need fo
my menus to be created as soon as the user selects to load the Add-In.
Can anyone help?

Thanks
 
F

Frank Kabel

Hi
this nworks for me. You can put your code in the workbook_open event of
your add-in. what exactly does not work?
 
S

Silas Mercer

It simply fails to execute the macro (as though nothing is happening).

I use the code above and my Create_Menu looks like this:

Sub Create_Menu()

Dim RefMenu As Object

Set RefMenu = CommandBars("Worksheet Men
Bar").Controls.Add(Type:=msoControlPopup, Before:=10)
With CLOO
.Caption = "RefMenu"
End With

End Sub

I've tried this in a simple xls workbook (non-add-in form) and i
doesn't work either, for some reason
 
M

Myrna Larson

Here's the code I use to create an item on the Tools menu for the Compare
utility that Bill Manville and I wrote. This may help you get started to
create your own menu.

In addition, here's a link to John Walkenbach's site. He has prewritten code
to create menus -- you put the required information on a worksheet in the
add-in. You usually don't have to modify John's code.

http://j-walk.com/ss/excel/tips/tip53.htm

My code to add an item to the Tools menu:

Const MacroName As String = "Compare"
Const ToolsMenuID As Long = 30007

Private Sub Workbook_Open()
DeleteMenuItem

With Application.CommandBars(1).FindControl(, ToolsMenuID) _
.Controls.Add(msoControlButton)
.Caption = MenuCaption
.OnAction = ThisWorkbook.Name & "!" & MacroName
End With
End Sub

Private Sub DeleteMenuItem()
Dim ToolsMenu As CommandBarControl

On Error Resume Next
Application.CommandBars(1).FindControl(, ToolsMenuID) _
.Controls(MenuCaption).Delete
On Error GoTo 0
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteMenuItem
End Sub
 
M

Myrna Larson

Where are you putting the code? It belongs in the ThisWorkbook module of the
add-in.
 
S

Silas Mercer

Hi Myrna,

Thank you for your help. It does appear that my code was residin
outside of the ThisWorkbook module, which is why it wasn't executing.

However, now I have another problem. I am using the following code t
create my menu when the workbook opens:

Sub Workbook_Open()

Dim CustomMenu As Object

Set CustomMenu = CommandBars("Worksheet Men
Bar").Controls.Add(Type:=msoControlPopup, Before:=10)
With CustomMenu
.Caption = "CustomMenu"
End With

End Sub

I am doing this in an xls workbook (not yet an xla) but for som
reason, I keep getting an error message with respect to my With...En
With statement:

Run-time error '91':

Object variable or with block variable not set

Yet when I run the same code embedded in my PERSONAL.xls workbook, i
works fine (!)

Can anyone tell me what I'm doing wrong here?

Thanks in advance
 
Top