Changing text in a Text Box

[Rocky McKinley]

This code works but I don't want to use "select" if I don't have to.
The Textbox is from the drawing toolbar.
How do I change the text without first selecting the textbox?


Sub SetTextBoxValue()
ActiveSheet.Shapes("Text Box 8").Select
Selection.Characters.Text = "My Text"
Range("A1").Select
End Sub

 

[Norman Jones]

Hi Rocky,

Try:

Sub SetTextBoxValue()
ActiveSheet.TextBoxes("Text Box 8").Text = "My Text"
End Sub

 

[Rocky McKinley]

Thanks, I wonder why when in the Vb editor when I type "ActiveSheet."
that "TextBoxes" doesn't appear in the list that appears?

 

[Norman Jones]

Hi Rocky,

Thanks, I wonder why when in the Vb editor when I type "ActiveSheet." that
"TextBoxes" doesn't appear in the list that appears?

There are two separate problems here:

(1) ActiveSheet will not provide intellisense prompts because VBA is not
aware if the sheet im question is a work sheet, a chart sheet, a macro sheet
or a dialog sheet.

A way round this would be:

Dim sh As Worksheet

Set sh = ActiveSheet

Then, typing sh. invokes thr relevant intellisense list, as VBA now knows
the sheet type.

(2) the second problem is that the TextBoxes are now classified as hidden
objects and so intellisense is provided. See 'Hidden Objects' in VBA help.

A way way round this would be:

Dim sh As Worksheet
Dim TBox As TextBox

Set sh = ActiveSheet

Set TBox = ActiveSheet.TextBoxes("Text Box 8")
TBox.Text = "My Text"

This way, as soon as TBox is typed, the TextBox intellisense list is
invoked.

 

[Rocky McKinley]

Thanks Norman

--
Regards,
Rocky McKinley

Hi Rocky,


There are two separate problems here:

(1) ActiveSheet will not provide intellisense prompts because VBA is not
aware if the sheet im question is a work sheet, a chart sheet, a macro
sheet or a dialog sheet.

A way round this would be:

Dim sh As Worksheet

Set sh = ActiveSheet

Then, typing sh. invokes thr relevant intellisense list, as VBA now knows
the sheet type.

(2) the second problem is that the TextBoxes are now classified as hidden
objects and so intellisense is provided. See 'Hidden Objects' in VBA help.

A way way round this would be:

Dim sh As Worksheet
Dim TBox As TextBox

Set sh = ActiveSheet

Set TBox = ActiveSheet.TextBoxes("Text Box 8")
TBox.Text = "My Text"

This way, as soon as TBox is typed, the TextBox intellisense list is
invoked.