Changing Text in Text Boxes on a Chart

by Allen Wyatt
(last updated October 29, 2018)

4

Don has multiple charts in a workbook. Each chart contains two text boxes with dates of the current data and dates that the chart was updated. Don would like to change all of the text in these chart text boxes with a macro but doesn't know the proper technique to perform the task.

This can, of course, be done with a macro, but you should understand that there is a way to do it without a macro. You can, if you'd like, tie what is shown in a text box to the contents of a cell. This means that you could make each of your text boxes "dynamic" so that all you need to do is change what is in the associated cell. That cell could contain a text value, a numeric value, a date, or a formula—it doesn't really matter.

Here's how to achieve this technique: Select the text box you want to associate with a cell by clicking on it a single time. The familiar handles should appear around the outside of the text box. Then, in the Formula bar, enter a cell reference. You can do this by entering it like this:

=$C$15

This ties the contents of the text box to whatever is in cell C15. You could also, if you prefer, delete whatever is in the Formula bar and, with the Formula bar active, click on the cell you want linked. The address for the cell should appear in the Formula bar. Press Enter and the task is done.

Now, type something into the associated cell. Whatever you type should appear immediately in the text box.

Before some potential macro-related answers can be provided, you'll need a bit of background concerning Excel's object model, which is accessible through VBA.

There are two types of charts that can be contained in a workbook: a chart sheet and an embedded chart. Chart sheets are represented by a Chart object and belong to the Charts collection. Embedded charts, on the other hand, are represented by a Chart object that belongs to the ChartObjects collection which, in turn, belongs to a worksheet object.

If you place a text box on an actual chart sheet, then it can be accessed in the following manner:

Sub FindTextBoxes1()
    Dim c As Chart
    Dim s As Shape
    Dim sMsg As String
    Dim sName As String

    For Each c In ActiveWorkbook.Charts
        For Each s In c.Shapes
            If s.Type = msoTextBox Then
                sMsg = s.TextFrame2.TextRange.Text
                sName = s.Name
                MsgBox "Text: " & sMsg, vbOKOnly, sName
            End If
        Next s
    Next c
End Sub

This macro steps through each chart sheet and then through each text box on each chart sheet. Whatever is in each text box is then displayed in a message box. If you want to change what is contained in a message box, all you need to do is set the Text property of the TextRange object.

Text boxes on embedded charts are a different story. Why? Because it is possible that the text box isn't really part of the chart, but is part of the worksheet. In other words, the "parent" of the text box could either be the embedded chart or it could be the worksheet.

A good way to find out if the text box is part of the embedded chart is to create a similar macro to the one just presented. This one, however, can step through each embedded chart in each worksheet and find each shape (text box) within that chart.

Sub FindTextBoxes2()
    Dim w As Worksheet
    Dim c As ChartObject
    Dim s As Shape
    Dim sMsg As String
    Dim sName As String

    For Each w In ActiveWorkbook.Worksheets
        For Each c In w.ChartObjects
            For Each s In c.Chart.Shapes
                If s.Type = msoTextBox Then
                    sMsg = "This text box is a child of the "
                    sMsg = sMsg & "embedded chart" & vbCrLf
                    sMsg = sMsg & "Text: " & s.TextFrame2.TextRange.Text
                    sName = s.Name
                    MsgBox sMsg, vbOKOnly, sName
                End If
            Next s
        Next c
        For Each s In w.Shapes
            If s.Type = msoTextBox Then
                sMsg = "This text box is a child of the "
                sMsg = sMsg & "worksheet" & vbCrLf
                sMsg = sMsg & "Text: " & s.TextFrame2.TextRange.Text
                sName = s.Name
                MsgBox sMsg, vbOKOnly, sName
            End If
        Next s
    Next w
End Sub

When you run the macro, you'll see a message box for each text box that is really a child of the embedded charts. Then, you'll see a message box for any text boxes that are a child of the worksheet. As in the previous macro, this one checks to make sure that the Shape object is really a text box before displaying any information about it. This is particularly important when stepping through each shape in the worksheet, because any embedded charts on the sheet are also considered part of the Shapes collection.

As earlier, to change the text contained in a text box, all you need to do is change the Text property of the TextRange object.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13377) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Breaking a Document Link

Word allows you to link external information into your documents. If you no longer need to maintain the active link, you ...

Discover More

Rounded Corners on Cells

As you are formatting a worksheet, Excel allows you to easily add borders to cells. Adding rounded corners to cells is a ...

Discover More

Files Opening Slowly If Many Files Exist

Managing large numbers of documents in Word can lead to some interesting challenges. One potential challenge is that your ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Adjusting Your View of 3-D Graphs

Do you use Excel's charting capabilities to display three-dimensional views of your data? The program provides a way that ...

Discover More

Copying a Chart and Related Shapes to a Word Document

Excel and Word are intended to work together, but sometimes it can seem that getting them to do so isn't that intuitive. ...

Discover More

Negatives in Pie Charts

Pie charts are a great way to graphically display some types of data. Displaying negative values is not so great in pie ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three more than 5?

2018-10-29 03:10:34

gerdami

I use MVP Jan Karel Pieterse' FlexFind free Excel add-in available at https://www.jkp-ads.com/OfficeMarketPlaceFF-EN.asp


2015-03-02 09:03:48

Glenn Case

Eulogio:

See the comments for tip# T011856 for the info you are after.

http://excelribbon.tips.net/T011856_Copying_Worksheets_in_a_Macro.html


2015-02-28 14:48:19

Roger Irvine

Hi,
Instead of giving us multiple bits of code to have to trawl through, how about just a small sample workbook that CONTAINS the relevant code - so that we can click 2 boxes and see the difference.
Then, if we want to go further into the code elements, THEN, we can, and easily.
I don't mind even donating a small amount if the codes are something that I (I repeat, that I) think are useful or that I could incorporate into a project.


2015-02-28 04:56:55

Eulogio Castillo

please can you guide how to execute or load in macro?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.