Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menumaker problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menumaker problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menumaker problem
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menumaker problem
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. "Bob Phillips" wrote in message ... 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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menumaker problem
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...nctionsAndSubs 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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menumaker problem
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" wrote in message ... 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. "Bob Phillips" wrote in message ... 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" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menumaker problem
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. --- 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menumaker problem
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) "Stuart" wrote in message ... 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. --- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming | |||
menumaker.xls | Excel Programming | |||
Menumaker.xls Create Macro on User Toolbar | Excel Programming |