![]() |
Editing XL95 custom menus with XL97
Problems are being encountered when moving an XL file with numerous
macros from XL95 to XL97. The XL file macros are linked to a pull down lists on the menu bar. An example of a screen view can be viewed at the web site: http://spreadsheet.home.comcast.net/Xlmenu1.gif Responses to prior posts have taught me that placing an ampersand before a letter in a menu name enables one to use a keyboard shortcut to that menu item. In XL95 I can use the MENU EDITOR to edit the menu items as shown in: http://spreadsheet.home.comcast.net/XLmenu4.gif Ampersands added using the XL95 menu editor then work in XL97, but I need a way to do it from within XL97. How does one customize / edit this menu from within XL97 when my custom list “ActionList” does not appear in the list of menu items. I similarly could not find it listed elsewhere. See http://spreadsheet.home.comcast.net/Xlmenu2.gif Thanks in advance... WindsurferLA |
I would start again and build the menus anew using the Commandbar facility
in XL97 on. John Walkenbach has a menu maker utility that you can utilise at http://www.j-walk.com/ss/excel/tips/tip53.htm By the way, your links didn't work for me. -- HTH RP (remove nothere from the email address if mailing direct) "windsurferLA" wrote in message ... Problems are being encountered when moving an XL file with numerous macros from XL95 to XL97. The XL file macros are linked to a pull down lists on the menu bar. An example of a screen view can be viewed at the web site: http://spreadsheet.home.comcast.net/Xlmenu1.gif Responses to prior posts have taught me that placing an ampersand before a letter in a menu name enables one to use a keyboard shortcut to that menu item. In XL95 I can use the MENU EDITOR to edit the menu items as shown in: http://spreadsheet.home.comcast.net/XLmenu4.gif Ampersands added using the XL95 menu editor then work in XL97, but I need a way to do it from within XL97. How does one customize / edit this menu from within XL97 when my custom list “ActionList” does not appear in the list of menu items. I similarly could not find it listed elsewhere. See http://spreadsheet.home.comcast.net/Xlmenu2.gif Thanks in advance... WindsurferLA |
Wind
Excel 5.0 and earlier used the "Menu Editor" to build menus. This is no longer available in versions newer than 5.0. You can't use the "delete" method or "reset" to get rid of the custom menu items. You also cannot edit these items to add the ampersand as you wish. Best thing to do is get rid of them and start over as Bob suggests or edit them in Excel 5.0 To get rid of the custom menu download Jim Rech's REMOVEMENUS.ZIP file from Stephen Bullen's site. See instructions and d/l file from: http://www.bmsltd.ie/MVP/Default.htm Gord Dibben Excel MVP On Thu, 27 Jan 2005 09:57:54 -0800, windsurferLA wrote: Problems are being encountered when moving an XL file with numerous macros from XL95 to XL97. The XL file macros are linked to a pull down lists on the menu bar. An example of a screen view can be viewed at the web site: http://spreadsheet.home.comcast.net/Xlmenu1.gif Responses to prior posts have taught me that placing an ampersand before a letter in a menu name enables one to use a keyboard shortcut to that menu item. In XL95 I can use the MENU EDITOR to edit the menu items as shown in: http://spreadsheet.home.comcast.net/XLmenu4.gif Ampersands added using the XL95 menu editor then work in XL97, but I need a way to do it from within XL97. How does one customize / edit this menu from within XL97 when my custom list “ActionList” does not appear in the list of menu items. I similarly could not find it listed elsewhere. See http://spreadsheet.home.comcast.net/Xlmenu2.gif Thanks in advance... WindsurferLA |
Gord Dibben wrote:
Wind Excel 5.0 and earlier used the "Menu Editor" to build menus. This is no longer available in versions newer than 5.0. You can't use the "delete" method or "reset" to get rid of the custom menu items. You also cannot edit these items to add the ampersand as you wish. Best thing to do is get rid of them and start over as Bob suggests or edit them in Excel 5.0 To get rid of the custom menu download Jim Rech's REMOVEMENUS.ZIP file from Stephen Bullen's site. See instructions and d/l file from: http://www.bmsltd.ie/MVP/Default.htm Gord Dibben Excel MVP On Thu, 27 Jan 2005 09:57:54 -0800, windsurferLA wrote: Problems are being encountered when moving an XL file with numerous macros from XL95 to XL97. The XL file macros are linked to a pull down lists on the menu bar. An example of a screen view can be viewed at the web site: http://spreadsheet.home.comcast.net/Xlmenu1.gif Responses to prior posts have taught me that placing an ampersand before a letter in a menu name enables one to use a keyboard shortcut to that menu item. In XL95 I can use the MENU EDITOR to edit the menu items as shown in: http://spreadsheet.home.comcast.net/XLmenu4.gif Ampersands added using the XL95 menu editor then work in XL97, but I need a way to do it from within XL97. How does one customize / edit this menu from within XL97 when my custom list “ActionList” does not appear in the list of menu items. I similarly could not find it listed elsewhere. See http://spreadsheet.home.comcast.net/Xlmenu2.gif Thanks in advance... WindsurferLA Starting over is not a pleasant thought as there are at least 100 macros, but it appears I have no alternative. At least I'll be able to reuse the macro code. Thanks for the help. |
Also, if you use John Walk's Menu Maker it will be relatively easy.
Try it out on a couple of the macros and get the fell of it. Then just jot down all your macros and menu structures on a bit of paper, and transcribing to the Menu Maker is a piece of cake. You are talking a few hours at most. -- HTH RP (remove nothere from the email address if mailing direct) "windsurferLA" wrote in message ... Gord Dibben wrote: Wind Excel 5.0 and earlier used the "Menu Editor" to build menus. This is no longer available in versions newer than 5.0. You can't use the "delete" method or "reset" to get rid of the custom menu items. You also cannot edit these items to add the ampersand as you wish. Best thing to do is get rid of them and start over as Bob suggests or edit them in Excel 5.0 To get rid of the custom menu download Jim Rech's REMOVEMENUS.ZIP file from Stephen Bullen's site. See instructions and d/l file from: http://www.bmsltd.ie/MVP/Default.htm Gord Dibben Excel MVP On Thu, 27 Jan 2005 09:57:54 -0800, windsurferLA wrote: Problems are being encountered when moving an XL file with numerous macros from XL95 to XL97. The XL file macros are linked to a pull down lists on the menu bar. An example of a screen view can be viewed at the web site: http://spreadsheet.home.comcast.net/Xlmenu1.gif Responses to prior posts have taught me that placing an ampersand before a letter in a menu name enables one to use a keyboard shortcut to that menu item. In XL95 I can use the MENU EDITOR to edit the menu items as shown in: http://spreadsheet.home.comcast.net/XLmenu4.gif Ampersands added using the XL95 menu editor then work in XL97, but I need a way to do it from within XL97. How does one customize / edit this menu from within XL97 when my custom list “ActionList” does not appear in the list of menu items. I similarly could not find it listed elsewhere. See http://spreadsheet.home.comcast.net/Xlmenu2.gif Thanks in advance... WindsurferLA Starting over is not a pleasant thought as there are at least 100 macros, but it appears I have no alternative. At least I'll be able to reuse the macro code. Thanks for the help. |
Bob Phillips wrote:
Also, if you use John Walk's Menu Maker it will be relatively easy. Try it out on a couple of the macros and get the fell of it. Then just jot down all your macros and menu structures on a bit of paper, and transcribing to the Menu Maker is a piece of cake. You are talking a few hours at most. I mistakenly started constructing new macros before studying John Walk's Menu Maker. After building much of my menu structure, I realized that once a custom menu structure is added to one workbook, it appears in all workbooks. I then realized that I could place menu items in a custom menu that is revealed and hidden as the workbook is opened and closed. However, John Walk's approach seems better as the menu items can't be inadvertently altered when running another workbook. I expect I'll go back and reconstruct menu's using his tool. Is there a way to capture the details of menu items that I've entered using standard XL97 procedures, so I can cut an paste them into sheets formated according to John Walk's procedure? Alternatively, is there a way to capture the XL95 menu items from my old workbook in either XL95 or XL97? Can you point me to any other ways, if there are any, are there to restrict custom menu items to specific workbook? Thanks for the help. WindsurferLA |
You could write a simple VB routine that would extract the details from your
menu, and put it in a worksheet. For example, say your menu is called "WindSurfer", this should be a start Private iLevel As Long Private iRow As Long Sub ReverseMenu() Const kMenu As String = "Windsurfer" Dim oCtl As CommandBarControl On Error Resume Next Application.DisplayAlerts = False Worksheets("Menu Maker").Delete Application.DisplayAlerts = True On Error GoTo 0 Worksheets.Add.Name = "Menu Maker" Range("A1").Value = "Level" Range("B1").Value = "Caption" Range("C1").Value = "Position/Macro" Range("D1").Value = "Divider" Range("E1").Value = "Face Id" iLevel = 1 iRow = 2 With Application.CommandBars("Worksheet Menu Bar") Range("A2").Value = iLevel Range("B2").Value = kMenu Range("C2").Value = "" Range("D2").Value = IIf(.Controls(kMenu).BeginGroup, "TRUE", "") Range("E2").Value = "" nextlevel .Controls(kMenu) End With End Sub Sub nextlevel(ctlParent As CommandBarControl) Dim ctl As CommandBarControl iLevel = iLevel + 1 For Each ctl In ctlParent.Controls iRow = iRow + 1 Cells(iRow, "A").Value = iLevel Cells(iRow, "B").Value = ctl.Caption Cells(iRow, "C").Value = ctl.OnAction Cells(iRow, "D").Value = IIf(ctl.BeginGroup, "TRUE", "") If ctl.Type = msoControlPopup Then Cells(iRow, "E").Value = "" Else Cells(iRow, "E").Value = ctl.FaceId End If If ctl.Type = msoControlPopup Then nextlevel ctl End If Next ctl iLevel = iLevel - 1 End Sub You will need to add the position of the first item, and modfy the onACtions if they have a full path, but it is a start. If you arer creating a custom toolbar, it will need some modification. -- HTH RP (remove nothere from the email address if mailing direct) "windsurferLA" wrote in message ... Bob Phillips wrote: Also, if you use John Walk's Menu Maker it will be relatively easy. Try it out on a couple of the macros and get the fell of it. Then just jot down all your macros and menu structures on a bit of paper, and transcribing to the Menu Maker is a piece of cake. You are talking a few hours at most. I mistakenly started constructing new macros before studying John Walk's Menu Maker. After building much of my menu structure, I realized that once a custom menu structure is added to one workbook, it appears in all workbooks. I then realized that I could place menu items in a custom menu that is revealed and hidden as the workbook is opened and closed. However, John Walk's approach seems better as the menu items can't be inadvertently altered when running another workbook. I expect I'll go back and reconstruct menu's using his tool. Is there a way to capture the details of menu items that I've entered using standard XL97 procedures, so I can cut an paste them into sheets formated according to John Walk's procedure? Alternatively, is there a way to capture the XL95 menu items from my old workbook in either XL95 or XL97? Can you point me to any other ways, if there are any, are there to restrict custom menu items to specific workbook? Thanks for the help. WindsurferLA |
On the second point, you could enable and disable your menu specifically on
activate/deactivate that workbook. And delete it on close. -- HTH RP (remove nothere from the email address if mailing direct) "windsurferLA" wrote in message ... Bob Phillips wrote: Also, if you use John Walk's Menu Maker it will be relatively easy. Try it out on a couple of the macros and get the fell of it. Then just jot down all your macros and menu structures on a bit of paper, and transcribing to the Menu Maker is a piece of cake. You are talking a few hours at most. I mistakenly started constructing new macros before studying John Walk's Menu Maker. After building much of my menu structure, I realized that once a custom menu structure is added to one workbook, it appears in all workbooks. I then realized that I could place menu items in a custom menu that is revealed and hidden as the workbook is opened and closed. However, John Walk's approach seems better as the menu items can't be inadvertently altered when running another workbook. I expect I'll go back and reconstruct menu's using his tool. Is there a way to capture the details of menu items that I've entered using standard XL97 procedures, so I can cut an paste them into sheets formated according to John Walk's procedure? Alternatively, is there a way to capture the XL95 menu items from my old workbook in either XL95 or XL97? Can you point me to any other ways, if there are any, are there to restrict custom menu items to specific workbook? Thanks for the help. WindsurferLA |
Bob Phillips wrote:
You could write a simple VB routine that would extract the details from your menu, and put it in a worksheet. For example, say your menu is called "WindSurfer", this should be a start Private iLevel As Long Private iRow As Long Sub ReverseMenu() Const kMenu As String = "Windsurfer" Dim oCtl As CommandBarControl On Error Resume Next Application.DisplayAlerts = False Worksheets("Menu Maker").Delete Application.DisplayAlerts = True On Error GoTo 0 Worksheets.Add.Name = "Menu Maker" Range("A1").Value = "Level" Range("B1").Value = "Caption" Range("C1").Value = "Position/Macro" Range("D1").Value = "Divider" Range("E1").Value = "Face Id" iLevel = 1 iRow = 2 With Application.CommandBars("Worksheet Menu Bar") Range("A2").Value = iLevel Range("B2").Value = kMenu Range("C2").Value = "" Range("D2").Value = IIf(.Controls(kMenu).BeginGroup, "TRUE", "") Range("E2").Value = "" nextlevel .Controls(kMenu) End With End Sub Sub nextlevel(ctlParent As CommandBarControl) Dim ctl As CommandBarControl iLevel = iLevel + 1 For Each ctl In ctlParent.Controls iRow = iRow + 1 Cells(iRow, "A").Value = iLevel Cells(iRow, "B").Value = ctl.Caption Cells(iRow, "C").Value = ctl.OnAction Cells(iRow, "D").Value = IIf(ctl.BeginGroup, "TRUE", "") If ctl.Type = msoControlPopup Then Cells(iRow, "E").Value = "" Else Cells(iRow, "E").Value = ctl.FaceId End If If ctl.Type = msoControlPopup Then nextlevel ctl End If Next ctl iLevel = iLevel - 1 End Sub You will need to add the position of the first item, and modfy the onACtions if they have a full path, but it is a start. If you arer creating a custom toolbar, it will need some modification. Thanks... will try your code... WindsurferLA |
windsurferLA wrote:
Bob Phillips wrote: You could write a simple VB routine that would extract the details from your menu, and put it in a worksheet. For example, say your menu is called "WindSurfer", this should be a start Private iLevel As Long Private iRow As Long Sub ReverseMenu() Const kMenu As String = "Windsurfer" Dim oCtl As CommandBarControl On Error Resume Next Application.DisplayAlerts = False Worksheets("Menu Maker").Delete Application.DisplayAlerts = True On Error GoTo 0 Worksheets.Add.Name = "Menu Maker" Range("A1").Value = "Level" Range("B1").Value = "Caption" Range("C1").Value = "Position/Macro" Range("D1").Value = "Divider" Range("E1").Value = "Face Id" iLevel = 1 iRow = 2 With Application.CommandBars("Worksheet Menu Bar") Range("A2").Value = iLevel Range("B2").Value = kMenu Range("C2").Value = "" Range("D2").Value = IIf(.Controls(kMenu).BeginGroup, "TRUE", "") Range("E2").Value = "" nextlevel .Controls(kMenu) End With End Sub Sub nextlevel(ctlParent As CommandBarControl) Dim ctl As CommandBarControl iLevel = iLevel + 1 For Each ctl In ctlParent.Controls iRow = iRow + 1 Cells(iRow, "A").Value = iLevel Cells(iRow, "B").Value = ctl.Caption Cells(iRow, "C").Value = ctl.OnAction Cells(iRow, "D").Value = IIf(ctl.BeginGroup, "TRUE", "") If ctl.Type = msoControlPopup Then Cells(iRow, "E").Value = "" Else Cells(iRow, "E").Value = ctl.FaceId End If If ctl.Type = msoControlPopup Then nextlevel ctl End If Next ctl iLevel = iLevel - 1 End Sub You will need to add the position of the first item, and modfy the onACtions if they have a full path, but it is a start. If you arer creating a custom toolbar, it will need some modification. Thanks... will try your code... WindsurferLA Per your suggestion, I downloaded John's menu maker. I can get John Walkenbach menu maker utility to work in a new blank XL97 workbook, but I can't get it to work in my workbook that already has numerous macros. The error message that I get is "compile error, user defined type not defined" and the line Dim MenuObject as CommandBarPopup is highlighted. I note under View\Toobars a toolbar called CommandBar is listed, possibly because I created it in response to the error message. I suspect a conflict between one of my macro set up and John's. ANY SUGGESTIONS? a screen image can be found at http://spreadsheet.home.comcast.net/MenuErr1.gif Thanks in advance... WindsurferLA |
Sounds as though the Office library reference has gone. In the VB IDE, goto
ToolsReferences. Is there a ticked entry for Microsoft Office n.n Object Library, where n.n is a version number? If not, scroll down un til you find it (it is probably near the top, else in alphabetical order) and check it. -- HTH RP (remove nothere from the email address if mailing direct) "windsurferLA" wrote in message ... windsurferLA wrote: Bob Phillips wrote: You could write a simple VB routine that would extract the details from your menu, and put it in a worksheet. For example, say your menu is called "WindSurfer", this should be a start Private iLevel As Long Private iRow As Long Sub ReverseMenu() Const kMenu As String = "Windsurfer" Dim oCtl As CommandBarControl On Error Resume Next Application.DisplayAlerts = False Worksheets("Menu Maker").Delete Application.DisplayAlerts = True On Error GoTo 0 Worksheets.Add.Name = "Menu Maker" Range("A1").Value = "Level" Range("B1").Value = "Caption" Range("C1").Value = "Position/Macro" Range("D1").Value = "Divider" Range("E1").Value = "Face Id" iLevel = 1 iRow = 2 With Application.CommandBars("Worksheet Menu Bar") Range("A2").Value = iLevel Range("B2").Value = kMenu Range("C2").Value = "" Range("D2").Value = IIf(.Controls(kMenu).BeginGroup, "TRUE", "") Range("E2").Value = "" nextlevel .Controls(kMenu) End With End Sub Sub nextlevel(ctlParent As CommandBarControl) Dim ctl As CommandBarControl iLevel = iLevel + 1 For Each ctl In ctlParent.Controls iRow = iRow + 1 Cells(iRow, "A").Value = iLevel Cells(iRow, "B").Value = ctl.Caption Cells(iRow, "C").Value = ctl.OnAction Cells(iRow, "D").Value = IIf(ctl.BeginGroup, "TRUE", "") If ctl.Type = msoControlPopup Then Cells(iRow, "E").Value = "" Else Cells(iRow, "E").Value = ctl.FaceId End If If ctl.Type = msoControlPopup Then nextlevel ctl End If Next ctl iLevel = iLevel - 1 End Sub You will need to add the position of the first item, and modfy the onACtions if they have a full path, but it is a start. If you arer creating a custom toolbar, it will need some modification. Thanks... will try your code... WindsurferLA Per your suggestion, I downloaded John's menu maker. I can get John Walkenbach menu maker utility to work in a new blank XL97 workbook, but I can't get it to work in my workbook that already has numerous macros. The error message that I get is "compile error, user defined type not defined" and the line Dim MenuObject as CommandBarPopup is highlighted. I note under View\Toobars a toolbar called CommandBar is listed, possibly because I created it in response to the error message. I suspect a conflict between one of my macro set up and John's. ANY SUGGESTIONS? a screen image can be found at http://spreadsheet.home.comcast.net/MenuErr1.gif Thanks in advance... WindsurferLA |
Bob Phillips wrote:
Sounds as though the Office library reference has gone. In the VB IDE, goto ToolsReferences. Is there a ticked entry for Microsoft Office n.n Object Library, where n.n is a version number? If not, scroll down un til you find it (it is probably near the top, else in alphabetical order) and check it. WindsurferLA .. I'm going to replicate this post as a new thread, as the current one is getting a bit long. You're right... but we still don't have solution. Office Library Reference is gone, but the problem seems yet more complex. When I go to the VB macro editor after opening the main workbook (that has been converted automatically from XL95 to XL97), the option to look at the Office Library Reference is grayed out. Thus I can't scroll through the list of Office Library References. I have also investigated the Office Library Reference in an test workbook (1) created from scatch using XL97, (2) incorporating the menu creator macros, and (3) in which the menu creator works just fine. When that workbook is open, the Office Library References are accessible, and the is an MSOffice 8.0 Object Library with a check mark as well as a MSOffic95 Object Library listed without a check mark. Checking the MSOffice95 Object Library did not seem to help. Another issue that might be related has to do with the list of saved programs. The main workbook uses the AutoSave macro to rename previously saved versions and save the most recent version as the prime version. I note that when I go back to open Excel, the main workbook is not listed in the list of recently saved files. Rather than trying to solve all these issues, how about if I just start with a virgin XL97 workbook, copy in the data and copy in the content of all the macro sheets. However, I must admit that I'm uncertain about my ability to copy in all the macro sheets without loosing some links. |
All times are GMT +1. The time now is 01:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com