XL: Unexpected Results When Pasting Linked Text Box

ID: Q106718

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In the versions of Microsoft Excel listed above, if you copy and paste a text box that has been linked to a cell in a worksheet, the link may not be updated properly, depending on where you paste the text box.

WORKAROUND

If a text box is pasted into another sheet, you cannot automatically maintain the link to the original sheet. To do this manually do the following (in this example, the text box is linked to Sheet1!$A$1):

Copy the text box.

Switch to the sheet (for example, Sheet2) where the text box will be pasted.

Paste the text box in the desired location.

While the text box is still selected, edit the formula in the formula bar so that it reads "=Sheet1!$A$1" (without the quotation marks).

Press ENTER.

The text box is now linked to the cell to which it was originally linked.

MORE INFORMATION

If you copy a text box from a worksheet or a Microsoft Excel version 4.0 macro sheet (for example, Sheet1) that has been linked to a cell in the same sheet with the formula =$A$1 or =Sheet1!$A$1, you will receive different results depending on where you paste the text box.

Worksheet or Microsoft Excel 4.0 Macro Sheet

The text box will become linked to cell A1 on the sheet where you paste it. For example, if you paste the text box in Sheet2, the text box will be linked to cell A1 in Sheet2.

Chart Sheet

The text box will lose its link to cell A1, but the value displayed in the text box will remain. For example, if cell A1 in Sheet1 contains the text "Sample," the text box will still say "Sample" when you paste it onto the chart.

Dialog Sheet

The text box will attempt to link to cell A1 in the dialog sheet. Because there are no cells in a dialog sheet, the text box will appear blank. In this case, use the workaround shown above to restore the proper link.

Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition

In these versions of Microsoft Excel, text boxes will remain linked to the correct cell as long as the reference to the cell also includes the sheet name. For example, if the link resembles the following


   =Sheet1!$A$1

the text box will link to that cell, no matter where you paste it.

REFERENCES

"User's Guide 1," version 5.0, page 231

Additional query words: 5.00a 5.00c 7.00a XL7 XL5

Keywords :
Version : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,98
Platform : MACINTOSH WINDOWS
Issue type : kbprb