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 D...@discussions.microsoft.com> 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 D...@discussions.microsoft.com> 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
>