Excel: Custom Commands Under Window Menu Change Position

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,"Window",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,"Window",B1)

   -or-

   =ENABLE.COMMAND(1,"Window",B1,FALSE)

If the following line is used

   =CHECK.COMMAND(1,"Window",B1,TRUE)

it will be ignored because, according to the above formulas, the 13th command (the value contained in B1) in the menu "Window" 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,"Window","My Command")

KBCategory: kbother
KBSubcategory:
Additional reference words: 2.20 3.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.