how can I work with worksheet textboxes in VBA?

[Pat D]

I have several textboxes on an Excel worksheet. Is there a way to access
(change contents, formatting,...) these textboxes from within my VBA code?

 

[JLGWhiz]

Using the control from the Controls Toolbox:

Sub txtbx()
Sheets(1).TextBox1.Text = "Hello"
MsgBox Sheets(1).TextBox1.Text
Sheets(1).TextBox1.Font.Italic = True
Sheets(1).TextBox1.Text = ""
End Sub

 

[Pat D]

I should've clarified a bit more...
what you are saying is workable for ActiveX or Form Controls.
I am referring to a textbox from the Insert menu (Office 2007).
The reason why I am using a plain text box is that I need to include a
bullet list in the text box, but I want to be able to alter the contents of
the bulleted list from my VBA code...

 

[Ken Johnson]

I have several textboxes on an Excel worksheet. Is there a way to access
(change contents, formatting,...) these textboxes from within my VBA code?

Text boxes are Shape objects.

Examples:
ActiveSheet.Shapes("Text Box 1").TextFrame.Characters.Text =
ActiveSheet.Shapes("Text Box 1").TextFrame.Characters.Text & Chr(10) &
"That's all folks."

Adds a new line and "That's all folk." to Text Box 1

ActiveSheet.Shapes("Text Box 1").TextFrame.Characters(20,
25).Font.Bold = True

Makes 25 characters, starting at the 20th character, Bold.

Ken Johnson

 

[Pat D]

Thanks!

Text boxes are Shape objects.

Examples:
ActiveSheet.Shapes("Text Box 1").TextFrame.Characters.Text =
ActiveSheet.Shapes("Text Box 1").TextFrame.Characters.Text & Chr(10) &
"That's all folks."

Adds a new line and "That's all folk." to Text Box 1

ActiveSheet.Shapes("Text Box 1").TextFrame.Characters(20,
25).Font.Bold = True

Makes 25 characters, starting at the 20th character, Bold.

Ken Johnson