How to insert text to a (worksheet) textbox using VBA in Excel?

Question

I have inserted a text box in my worksheet, using the menu "Insert/Text Box" at the ribbon of Excel 2007. It has automatically been named as "TextBox 17". I am trying to set its value using my VBA code, but I can't figure out the way to do it. I even tried to run a single-line routine just to feed the textbox with some text (like the following and other combinations) but failed.

Sub test()
    Sheets(1).Shapes.item(14).Text = "eventually some text"
    Sheets(1).Shapes(14).Text = "eventually some text"
    Worksheets(1).Shapes(14).Value = "eventually some text"
    Sheets(1).Shapes("TextBox 17").Text = "eventually some text"
    Sheets(1).Shapes("TextBox 17").ControlFormat.Value = "eventually some text"
    Worksheets(1).Shapes(14).TextFrame.TextRange.Text = "eventually some text"
End Sub

While in research about this, I found answers only about activeX textboxes. But as far as I understood, the textbox I have inserted is not of this kind.

Can anybody help?

Fabio Poloni · Accepted Answer · 2015-03-30 12:35:25Z

3

This seems to be working with no problem:

Sheets(1).Shapes(14).TextFrame.Characters.Text = "xxx"

edited Mar 30, 2015 at 12:35

Fabio Poloni

8,3815 gold badges46 silver badges74 bronze badges

answered Mar 28, 2015 at 22:20

fontan

8610 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Solaris_9 Over a year ago

object doesn't support this method or property error, any idea?

PeterT · Accepted Answer · 2015-03-28 22:21:50Z

2

I was able to set the text box contents with

Sub test()
    Sheets(1).Shapes.Item(14).TextFrame.Characters.Text = "eventually some text"
End Sub

answered Mar 28, 2015 at 22:21

PeterT

8,5971 gold badge19 silver badges46 bronze badges

Comments

GTaylor · Accepted Answer · 2023-03-16 18:19:02Z

0

You can put the text in a cell (maybe the one obscured by the text box) and set the text box value to the content of the cell (=$B$7). The cell's text color can be changed to white to hide it, too.

The drawback is that it's vulnerable to a user changing it manually.

answered Mar 16, 2023 at 18:19

GTaylor

1217 bronze badges

Collectives™ on Stack Overflow

How to insert text to a (worksheet) textbox using VBA in Excel?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related