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, _
Befo=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
"Stuart" wrote in message
...
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.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.707 / Virus Database: 463 - Release Date: 15/06/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.707 / Virus Database: 463 - Release Date: 15/06/2004