Addin menu creation

[Zack Barresse]

Hi everybody! :006:

Got another question here. I'm working on an add-in, for my work this time. And I'm using J-Walks menu creator; I didn't want to develop one myself! Here: http://www.j-walk.com/ss/excel/tips/tip40.htm

I can get everything to work OK, the structure is good, menu, icons (faceID's), macros run just fine from the addin. It just won't create itself when I load the addin anymore. I'm not really sure what to do with this as I haven't developed many addins. I'm calling the macro to create the menu on workbook_open, which calls the menu delete to prevent duplicates. If I run the sub manually, it works fine, but automatically is a different story.

Anybody think they can help me out? Even a run through the basic steps to see if I missed something would be helpful! It's probably something really obvious, and I'm gonna feel really stupid about it. Thanks. :)

[Tommy]

Let's see what you got :)

[Zack Barresse]

In the .xls file, I've got:

In ThisWorkbook module:

Private Sub Workbook_Open()
Call CreateMenu
End Sub

In a standard module (courtesy of J-Walk):


Option Explicit
Sub CreateMenu()
' This sub should be executed when the workbook is opened.
' NOTE: There is no error handling in this subroutine
Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup
Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
Call DeleteMenu
Row = 2
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With
Select Case MenuLevel
Case 1 ' A Menu
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption
Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True
Case 3 ' A SubMenu Item
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End Sub

Sub DeleteMenu()
Dim MenuSheet As Worksheet
Dim Row As Integer
Dim Caption As String
On Error Resume Next
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
Row = 2
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
If MenuSheet.Cells(Row, 1) = 1 Then
Caption = MenuSheet.Cells(Row, 2)
Application.CommandBars(1).Controls(Caption).Delete
End If
Row = Row + 1
Loop
On Error GoTo 0
End Sub

I've got macros in other modules in this file. In the sheet set-up, I've got the menu layout/structure. I can upload a copy of the book if that'd help.

I'm then saving the book as an .xla, adding it to my add-in list via the Browse button.

[Tommy]

If you put error checking in the code above, and when the code is run as .xla, does thisworkbook contain MenuSheet? :confused: This may seem silly but the .xla's I have seen do not have a sheet embedded in them. Or do you create the sheet each time? :dunno


ThisWorkbook.Sheets("MenuSheet")

[Zack Barresse]

You know, this thing is driving me nuts. I deleted the old add-in ver., saved the new ver. and all is fine now! Now it pops up every time! Oh well..

And the add-in does show a ThisWorkbook module. Is that abnormal? I see it on every other add-in I've got also. And no, I don't create one every time.

This has been an odd one. Thanks for your help/patience Tommy!

[Zack Barresse]

I've got another one for you though. Now that it loads up everytime, how would I delete the menu when taking off the add-in from the add-in list? I tried a workbook_close() event, but I don't think that's the key.

Never done this before, so it's new territory for me here. Be gentle :rolleyes:

[Tommy]

Is that abnormal? :dunno That's why I asked :)

Let me look at it, on first glance it seems like the on unload event but I know that isn't right.

[Zack Barresse]

The files are uploaded. The .xls file is the one used to create the .xla. The add-in should self load ok. I put the workbook_close event on it, but doesn't seem to be working.

And (ahem) ignore the HPP macro. It was my first macro I ever wrote. It's ugly :rolleyes:

Let me know what you think.

[Tommy]

When the addin is unchecked from the add-in dialog box, it throws an uninstall event. There is a check you can make with the installed option it is true if it is installed and false otherwise.

Umm not being picky :roll: but on this :PositionOrMacro = .Cells(Row, 3) the value in that cell is 14 I only have 10 (std) this may cause some trouble for you later, just an observation.


Private Sub Workbook_AddinUninstall()
Call DeleteMenu
MsgBox "Gone"
End Sub

[Zack Barresse]

Good point Tommy! This is my workaround:

1) After the Call DeleteMenu, type:


Call posMacro

2) Place this macro in the same module:


Sub posMacro()
Dim i As Integer
i = Application.CommandBars(1).Controls.Count
Cells(2, 3).Value = i + 1
End Sub


That took care of that :) Excellent point.

And that code worked beautifully! I think it's about done! I can't thank you enough Tommy! THANK YOU!

[brettdj]

Hi Zack,

My initial thought when I saw this was that you may be loading the addin with the fast load option /f in the registry settings. Not the case but I thought I'd add this to the thread in case it applies to someone else.

Cheers

Dave

From
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/SFA96.asp

Option Switches for the OPEN Entry
You can use the /r and /f option switches, alone or together, to modify the behavior of the OPEN entry. The /r option switch opens the file as read-only. The /f (fast load) option switch places template documents in the New dialog box (accessed from the File menu). When used to open a macro sheet containing custom functions, the /f option switch places those functions in the Function Wizard dialog box. When you use one of the custom functions in a specified document, the macro sheet containing the function is opened automatically.

For example, to open the file BUDGET.XLS as read-only every time Microsoft Excel is started, use the following value:

/r c:\excel\budget92.xls

To enter more than one OPEN entry, you must number them sequentially. For example, OPEN, OPEN1, OPEN2, and so on.

If you have a macro sheet named CUSTOM.XLM that contains function macros, the following value will cause it to open automatically, and the functions it contains will be added to the User Defined function category in the Function Wizard dialog box:

/f c:\excel\custom.xlm

To use the /f switch with add-in macro sheets (.XLA), you must define the name __DemandLoad (the string "DemandLoad" preceded by two underscores) for the workbook. Microsoft Excel checks to see if the name is defined; the actual definition that you use is not important. You can define __DemandLoad as the Boolean value TRUE, for example.


When an OPEN entry is used to open an add-in macro sheet on which the name __DemandLoad is defined, custom functions are displayed in the Function Wizard dialog box, but the add-in is not actually loaded until one of its functions is recalculated.

[Zack Barresse]

Thanks Dave, that's a great piece of information!

But no, I wasn't fast loading it. It's just a few useful macros I've whipped up and then some work related techie stuff for reporting.

I had the macros on toolbar shortcuts, but felt they were taking up valuable *real estate* and decided to move them to a custom menu. I'm going to get to liking these custom menus :yes They're pretty slick!

Thanks for the input!!!

[Tommy]

Zack,
Glad it works like you want it to. The fix you posted works like a charm also :).