Editing Custom Worksheet Menu Bar in XL97 & Associtated Macros

[windsurferLA]

I need to transition an XL95 file to XL97 as part of a move from Win98SE
to WinXP machine. I have lots of experience writing macros for XL95,
but I have not yet mastered finding my way around the Visual Basic
Editor. When I open the XL95 file in XL97, the Worksheet Menu Bar
includes my custom options and the macro links work with some exceptions.

I need to explore those macros that do not work. As the sheet has over
100 macros, it can be tough to remember the name for each macro. In
Excel95, one could go to the EDIT MENU and see the precise name of the
macro that was assigned to the Menu Bar task. I've not been able to do
that in XL97. When I open VIEW/TOOLBARS/CUSTOMIZE, a check appears next
to "worksheet menu." However the only options are NEW / RESET / ATTACH;
there is no EDIT option. I assume that when I find the EDIT option
location, I'll be able to see the name associated with each task.

If I click on "ATTACH," it shows that there are no "menus" attached to
the file that is open. However, I know the my custom Worksheet Menu Bar
must be attached because it is displayed. What am I missing?

In XL97, I could access the menu tasks using the key board. Alt-L got
me to the special LIST function on the menu bar, and then the next key
typed would get me to the item within that list, provided it started
with the same letter and there were no conflicts. I suspect that option
must be disabled by the settings of my copy of XL97. How does one
enable that function?

 

[windsurferLA]

I've solved my problem of how to search the macros. It was just a
question of searching all the menu options. I also solved my XL97
incompatibility problem; the name of one of my macros was the same as a
name adopted for a command in XL97.

REMAINING ISSUE NEEDING HELP -
In XL95, I could access the menu tasks using the key board. Alt-L got
me to the custom LIST function on the menu bar because it LIST begins
with "L", and then the next key typed would get me to the item within
that list, provided: (1) it started with the same letter and (2) there
were no conflicts. For example, if the first item in the sub list was
"Sort", typing "S" would bring me to the head of that sublist. How
does one add that function to a submenu in XL97?

 

[Bob Phillips]

Add an & before the key letter in the caption, such as

.Caption = "&List"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Gord Dibben]

One more item of information....


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(as you have learned).

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

 

[windsurferLA]

Adding an ampersand (&) before character is simple enough. Thanks for
the tip. I'm just surprised I couldn't find it in either of my two very
thick Excel 97 books..

However, adding an ampersand assumes you can edit the custom menus
sub-categories. When I go to customize the Menu Bar, the custom
category is not one of those listed, and hence there is no option to
edit its subcategories.

Once in XL97, is the only way to revise the custom category other than
by rebuilding from scratch. I see how to delete using Jim Rech's
REMOVEMENUS.ZIP (see Gord Dibbens comment).

 

[Bob Phillips]

I don't know as I don't have such a menu to test with, but can you not
access the control through its commandbar, circa

With Application.commandbars("myBar").Controls("myControl")
.Caption = "&List"
End With


where myBar is the commandbar name, myControl is the control name (as seen
on its caption). If it were sub menus, it would be


With Application.commandbars("myBar").Controls("myControl")
.Controls("mySubControl").Caption = "&List"
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)