By J. Carlton Collins, CPA
Q. Is it possible to link the contents of a text box in Excel to data in a cell?
We use cookies to provide and improve our services. By using our site, you consent to our Cookies Policy. Accept Learn more Learn more.
A. Yes, you can link the contents of an Excel text box to data in a cell as follows:
1. Insert a text box. Insert a text box in Excel from the Insert tab by selecting Text, Text Box How to build animation in powerpoint. , and then use your mouse to drag to a region on your worksheet.
2. Insert a formula in the text box. With the Text Box still selected, press the F2 key. This will activate the formula bar (indicated by a blinking cursor in the formula bar). Write your desired formula (in the example shown above, I entered the formula =B2) and press Enter. Thereafter, the text box will display the data contained in the linked cell (405,843 in this example), even if the contents of that cell change.
Additional comments about text boxes:
a. The linked text box will continue to work even if you move it to another worksheet or to another workbook.
b. The linked text box will work even if you create the link in a different worksheet or workbook.
c. This type of link also works with other shapes, such as the four example speech bubbles pictured below. (In this screenshot, I also applied a few of the available formatting effects involving colors, shadows, bevels, and glow effects.)
d. With a little tech-savvy trickery, it is possible to combine text and numbers to embed a full sentence in a text box or speech bubble. For example, below in cell A4, I have used the Concatenate symbol (the '&' symbol) to combine both text and numbers. I also used the Text function to control the formatting of the values included in the sentence. I then embedded the resulting formula in the speech bubble and included a clip art image of myself. The result is an image with a speech bubble that changes automatically as the values in the worksheet change.
This type of effect may be useful in certain situations to help emphasize key results produced in a workbook. You can download this example workbook at carltoncollins.com/textbox.xlsx.
About the author
J. Carlton Collins ([email protected]) is a technology consultant, a CPE instructor, and a JofA contributing editor.
Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to [email protected]. We regret being unable to individually answer all submitted questions.
I have a textbox on Worksheet A. The textbox name is TextboxB. I am trying to use VBA to add text to the end of the textbox without overwriting the text that is already in there. Please keep in mind these are textboxes on worksheets that are shapes, not controls.
Using the following put's the text in , but deletes all other text first.
This is what I want to happen
TextBox A (pre code run)
|***********************|
| This text is here |
| |
|_______________________|
TextBox A (post code run)
|***********************|
| This text is here |
| New Text |
|_______________________|