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

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" wrote:

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

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...

"JLGWhiz" wrote:

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" wrote:

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

On Mar 20, 10:46 pm, Pat D <Pat wrote:
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

Thanks!

"Ken Johnson" wrote:

On Mar 20, 10:46 pm, Pat D <Pat wrote:
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