Changing text in a Text Box
 

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

--
Regards,
Rocky McKinley

Changing text in a Text Box
 

Hi Rocky,

Try:

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

---
Regards,
Norman



"Rocky McKinley" wrote in message
...
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

--
Regards,
Rocky McKinley

Changing text in a Text Box
 

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

--
Regards,
Rocky McKinley


"Norman Jones" wrote in message
...
Hi Rocky,

Try:

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

---
Regards,
Norman



"Rocky McKinley" wrote in message
...
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

--
Regards,
Rocky McKinley

Changing text in a Text Box
 

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 he

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

---
Regards,
Norman



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

--
Regards,
Rocky McKinley


"Norman Jones" wrote in message
...
Hi Rocky,

Try:

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

---
Regards,
Norman



"Rocky McKinley" wrote in message
...
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

--
Regards,
Rocky McKinley

Changing text in a Text Box
 

Thanks Norman

--
Regards,
Rocky McKinley


"Norman Jones" wrote in message
...
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 he

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

---
Regards,
Norman



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

--
Regards,
Rocky McKinley


"Norman Jones" wrote in message
...
Hi Rocky,

Try:

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

---
Regards,
Norman



"Rocky McKinley" wrote in message
...
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

--
Regards,
Rocky McKinley