Microsoft KB Archive/117232

Microsoft Knowledge Base

XL: Reference Edit Box Returns Incorrect Reference

Last reviewed: September 13, 1996
Article ID: Q117232

The information in this article applies to:

• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In Microsoft Excel, if you use a reference edit box (item 10) in a user- defined dialog box to return a reference to a range on a worksheet, and if the returned worksheet name contains spaces (for example, if the worksheet is named "My Worksheet"), the returned reference is incorrect.

CAUSE

This behavior occurs when you use a reference edit box to reference a range on a worksheet in the same workbook that contains the dialog definition table that is used to create the reference edit box. When the sheet name contains a space, the reference returned by the reference edit box does not include the workbook name inside the apostrophe characters the way it should. For example, when you run a macro that displays a reference edit box, and you select the range A1:C3 on the worksheet "Two Words" in the workbook BOOK1.XLS (the same workbook that contains the macro), the range appears in the reference edit box as follows:

   'Two Words'!$A$1:$C$3

However, the reference that is returned by the reference edit box is as follows:

   [BOOK1.XLS]'Two Words'!R1C:R3C3

Note that the book name is outside the apostrophe characters, creating an incorrect reference to the range on the worksheet.

WORKAROUND

To avoid this problem, use a dialog sheet to create the reference edit box instead of a dialog box definition table on a macro sheet. To create a reference edit box in a dialog box using a dialog sheet, do the following:

1. In a new dialog sheet, choose the Edit Box button from the Forms toolbar.
2. In the dialog box, drag the insertion point to create an edit box.
3. From the Format menu, choose Object. Select the Control tab. Under Edit Validation, select the Reference option. Choose OK.

When you run this dialog box, you can click in the reference edit box, and select a range on a worksheet. When you use the reference edit box to select a range on a worksheet in the workbook that contains the dialog box, and the worksheet name contains a space, and the following reference is returned:

   'Two Words'$A$1:$C$3

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

REFERENCES

"Visual Basic User's Guide," version 5.0, pages 236-237

KBCategory: kbusage KBSubcategory: Additional reference words: 5.00 5.00a 5.00c 7.00 7.00a wrong invalid error 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: September 13, 1996 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.