John's code depends on a worksheet that contains the level, captions, and so
forth.

The first thing his code does is work with this line:

Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Befo=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption


Application.commandbars(1) is the worksheet menubar--where file|edit|view, ...
are found.

Then it cycles through that worksheet looking for items to add under that
dropdown (level 2's) and then for subitems to add to each item (level 3).


Dan Thompson wrote:

Interesting I have never used the Call function to create a menu I will have
to check it out sounds like a good way to create and remove menu's.
My menus do not go away when I close the workbook but your comment made me
relize that is probably because I have specified the persist or permanent
option when the menu is created instead of using the temporary option.

Thanks again for your help.
I am still confused as to how this Add-In I downloaded is able to create the
menu Item. I have looked through the code over and over and maybe I am just
blind lol but I do not see any code for the creation of the menu item for the
Add-In and yet it creates the menu when I install the Add-In and removes the
menu Item when I uninstall the Add-In.

"Dave Peterson" wrote:

Look at the code in John Walkenbach's workbook.

You'll see this under ThisWorkbook:

Private Sub Workbook_Open()
Call CreateMenu
MsgBox "A new menu (MyMenu) was created.", vbInformation
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub

And in the CreateMenu code (in the General module), you'll see this:

Option Explicit
Sub CreateMenu()
'a few lines of code that I snipped
' Make sure the menus aren't duplicated
Call DeleteMenu

The first step in creating a new menu is to delete the old one. And closing the
workbook will delete the menu, too.

So I'm guessing that you don't have the equivalent in your code.

Dan Thompson wrote:

Thanks for the links Dave I will check them out.
btw I did add the on workbook open to my Add-In application and it works for
adding the menu command when you install the Add-In however it does not
remove it from the menu bar when you un check the Add - In to remove it so if
I install and remove it more than once I end up with several duplicate
buttons for ruing the macro / Add-In and yet the one I was working with that
I sent you a link too seems to add the menu command when you add the Add-In
and than Remove the Menu button/command when you remove the Add-In Do you
know how to do that ?

Dan Thompson.

"Dave Peterson" wrote:

If you want to give the users a way to access the macros:

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

Dan Thompson wrote:

I am having trouble creating an Add-In application.
I have complied my VBA code and saved my workbook as an XLA file and then
added MyApp.XLA through the Add-In Manager it shows up in Add in manager but
there is no listing for it under the tools Menu or in any menu. So I can not
run it by just selecting Tools and than MyApp. How do I get it to show up
there. And also when I slelect it in the Add-in manager you know with the
check boxes beside each add-in there is no discription for my add-in in the
description box below.

Can Anyone Help ??

Dan Thompson.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson