Microsoft KB Archive/57389

{| = Excel: Custom Commands Under Window Menu Change Position =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q57389

SUMMARY
The ADD.COMMAND function returns the line of the menu on which the first custom command is placed. When adding commands to the built-in Window menu, ADD.COMMAND will add the item to the bottom of the menu (as on other menus). However, if another document is opened or if an existing document is closed or hidden, the list of opened documents will move to the bottom of the menu, moving the custom command up.

This in itself causes no problems in the execution or use of the command. If, however, the CHECK.COMMAND, ENABLE.COMMAND, or DELETE.COMMAND function is used in the macro and the function uses the command position (the position the command occupies on the menu), either a macro error will occur or the function will be ignored. The above functions behave normally if the command name is used rather than its position.

MORE INFORMATION
For example, if the Window menu appears as follows

WINDOW Help... New Window Show Clipboard Show Info Arrange All Hide Unhide *Macro1 Worksheet1 and after running the following command in cell B1  =ADD.COMMAND(1,&quot;Window&quot;,D1:E1) cells D1:E1 contain the following | My Command    |  My_Macro   | cell B1 (which contains the formula) returns a 13, indicating that the command was added to line 13 of the menu. This is true immediately after the function is first executed (as shown below): WINDOW Help... New Window Show Clipboard Show Info Arrange All Hide Unhide *Macro1 Worksheet1 My Command             <--the custom menu item, line 13 However, if another document (worksheet, macro, or chart) is opened, or an existing one is closed, the list of documents is moved to the bottom of the menu, as shown below: WINDOW Help... New Window Show Clipboard Show Info Arrange All Hide Unhide My Command             <--the custom menu item, line 11 Macro1 Worksheet1 *Worksheet2 The custom command is now on line 11, while the formula in cell B1 of the macro continues to return the value of the original position of the command. If either of the following lines are used in the macro, a macro error occurs: =DELETE.COMMAND(1,&quot;Window&quot;,B1)

-or-

=ENABLE.COMMAND(1,&quot;Window&quot;,B1,FALSE) If the following line is used =CHECK.COMMAND(1,&quot;Window&quot;,B1,TRUE) it will be ignored because, according to the above formulas, the 13th command (the value contained in B1) in the menu &quot;Window&quot; is to be altered; however, the 13th command is now a document name, rather than the custom menu item. If the formula uses the command name, rather than its position, the function will execute without error, as in the following:

=DELETE.COMMAND(1,&quot;Window&quot;,&quot;My Command&quot;)
 * }