Menumaker problem

[Stuart]

I'm using John Walkenbach's excellent Menumaker utility.
It has been working fine until I made a couple of alterations
......basically I added three more options.

When I open excel2000 the addin loads the menu item,
together with the submenu options. However my new
three options do not load.

If I now run the menu code manually, then the options
do appear, and they work ok.

Here's the layout in the addin sheet that the menu code
takes it's data from:

Level Caption Position/Macro Divider FaceID
1 &Valuation BofQ 10
2 &Open a Valuation OpenExistingValuation TRUE 23
2 &Close a Valuation (and Save Changes) Protect_Close_SaveVln TRUE 3
2 &Close a Valuation (without Saving Changes) Protect_Close_Vln TRUE
232
2 &With the Open Valuation... TRUE
3 &Remove all Protection RemoveProtection TRUE
3 &Protect the Valuation AddProtection TRUE
3 &Add a Standard Page AddStandardBofQPage TRUE #
3 &Add a Collection Page AddCollectionBofQPage TRUE #
3 &Add a Summary Page AddSummaryBofQPage TRUE #
2 &Choose a Valuation to Open and.. TRUE
3 &Calculate the Valuation Choose_Calculate_Valuation TRUE 681
3 &Print a Valuation (with options) PrintMultiBofQWorkbooks TRUE
2 &Valuation Utilities..Choose a Valuation and ... TRUE
3 &Check BofQ Total CheckTenderBofQTotal TRUE
3 &Copy Tender Quantities into Valuation Column
CopyTenderToVlnQuantities TRUE
3 &Remove Valuation Quantities and Client Totals
RemoveTenderBofQTotalAndVlnQuantities TRUE
3 &Remove Client Total RemoveTenderTotals TRUE
3 &Remove Valuation Quantities RemoveValuationQuantities TRUE


The new entries ara marked as '#'.

Would be grateful for help, please.

Regards.

 

[Stuart]

I guess showing the code might help ......

Option Private Module
Option Explicit

Sub CreateValuationMenu() 'KEEP 31/05/04
' 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

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("VlnBofQMenu")
''''''''''''''''''''''''''''''''''''''''''''''''''''

' Make sure the menus aren't duplicated
Call DeleteValuationMenu

' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

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
' Add the top-level menu to the Worksheet CommandBar
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

Apologies, and

Regards

 

[Bob Phillips]

What is # supposed to signify, this is not a valid FaceId?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Stuart]

Bob,

Apologies for the linewrap in the original post,
and my lack of clarity.

As I previously said, 3 new menu entries are causing
the problem.

'#' indicates my new menu entries.

Regards.

 

[David McRitchie]

Hi Stuart,
What does it do instead of work.

Just the three lines inserted into the spreadsheet, no
changes to the macro -- right?

Try running this macro to see what you actually have available
in your open workbooks.
Subroutine and Function Table for Open Workbooks (#ListFunctionsAndSubs)
Build Table of Contents, similar listings, working with Hyperlinks
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#ListFunctionsAndSubs

I don't know what you would get if you have duplicate macro names
since you are not including the workbookname. You will definitely have
a problem if you have the same macro in the same module because you
cannot address one or the other specifically. You can address separate
workbookbooks and modules.

 

[Bob Phillips]

Okay, so I remove the # from the 3 items, and they appear in the menu for me
okay.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Stuart]

Thanks for taking the time and trouble.

What I have is this:

Original Situation:
Open Excel2000 and ...fine ...addin(s) load.
No changes made ....same addins are loading
No apparent problems

Changed Situation:
With one addin, I change the the menu items
(for that addin..... as per ###)
Set addin back to True
Save Project
Close Excel

If I now open Excel, my new menu items are not
displayed.

If I now enter the VBE and manually step through
the menu-creation code for that addin, then the
changes work.

Confused.

Regards.

 

[Bob Phillips]

It sounds to me that you are not running your macro automatically on
start-up.

You need to call the CreateMenu macro from within the Workbook_Open event in
the ThisWorkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)