Reference to free-form textboxes
 

I insert a text box into Sheet1 of Excel and want to assign a value to this
text box via Excel automation from another program. I have tried the
following syntax without success:
oExcel.ActiveWorkbook.Sheets(1).Textboxes("TextBox 1").Value="Some
Text"

What is the correct syntax (or object path) to refer to this text box?

Thanks in advance.
KM

Reference to free-form textboxes
 

Hi

I assume that the workbook holding the textbox is the active workbook.

oExcel.ActiveWorkbook.Sheets(1).TextBox 1.Value="Some Text"

Regards,

Per

"Krygim" skrev i en meddelelse
...
I insert a text box into Sheet1 of Excel and want to assign a value to this
text box via Excel automation from another program. I have tried the
following syntax without success:
oExcel.ActiveWorkbook.Sheets(1).Textboxes("TextBox 1").Value="Some
Text"

What is the correct syntax (or object path) to refer to this text box?

Thanks in advance.
KM

Reference to free-form textboxes
 

oExcel.ActiveWorkbook.Sheets(1).TextBox1.Value="So me Value"


"Krygim" wrote:

I insert a text box into Sheet1 of Excel and want to assign a value to this
text box via Excel automation from another program. I have tried the
following syntax without success:
oExcel.ActiveWorkbook.Sheets(1).Textboxes("TextBox 1").Value="Some
Text"

What is the correct syntax (or object path) to refer to this text box?

Thanks in advance.
KM

Reference to free-form textboxes
 

Hi Joel and Per,

Thanks for your response. I tried the following command in the Immediate
window of Excel:
ActiveWorkbook.Sheets(1).TextBox1.Value="Some Value"

I got the error message: "Method or data member not found"
I have already named "Text Box 1" as "Textbox1"

But the following command was okay:
ActiveWorkbook.Sheets(1).Range("A1").Value="Some Value"

Any idea?

KM


"Joel" wrote in message
...
oExcel.ActiveWorkbook.Sheets(1).TextBox1.Value="So me Value"


"Krygim" wrote:

I insert a text box into Sheet1 of Excel and want to assign a value to
this
text box via Excel automation from another program. I have tried the
following syntax without success:
oExcel.ActiveWorkbook.Sheets(1).Textboxes("TextBox
1").Value="Some
Text"

What is the correct syntax (or object path) to refer to this text box?

Thanks in advance.
KM

Reference to free-form textboxes
 

Hi Joel,

I tried in both Excel 2003 and 2007, the following one line statement
doesn't work.
ActiveWorksheet.Shapes("Text Box 1").Characters.Text="Some text"

After renaming the box to "Textbox1" and changing the statement accordingly
as follows, it still failed.
ActiveWorksheet.Shapes("Textbox1").Characters.Text ="Some text"

I have made sure no leading and trailing are in the name. It seems that 2
lines are necessary. Can you try in your Excel and confirm?

Thanks,
KM


"Joel" wrote in message
...
My instructions were for a text box like the one you are using. Most
people
don't realize you can get the properties of these boxes. It looks like
the
problem was with the spacing. Your latest code has a space between the
work
text and box. You would of seen the space by looking at the properties.

You can also do the same thing in one instruction

ActiveWorksheet.Shapes("Text Box 1").Characters.Text="Some text"

There is no need to select something and then perform an action. The
record
macro creates two instructions (select and action) where only one is
required. I like to take record macros and siomplify the code where it
makes
sense. Some instructions have to be done in two steps like a Paste
Special,
but most instructions do not.


"Krygim" wrote:

Hi Joel,

I am sorry that I didn't state my question clearly. I am talking about a
textbox floating over the cells of a normal worksheet rather than on a
UserForm.

I have just found a way to update its content as follows:
ActiveWorksheet.Shapes("Text Box 1").Select
Selection.Characters.Text="Some text"

My problem is solved. However I don't know if there is any simpler way.

Thanks again.
KM


"Joel" wrote in message
...
the question is if you really have a text box on the worksheett and
what
is
the real name. Text boxes havve both a caption and a name, you need to
check
what the name actually is. You can also change the name to anything
you
like.

You need to check the properties of the text box by doing the following
on
the worksheet and get into the "Design Mode"

1) Worksheet menu - View - Toolbars - Control Toolbox
2) Press triangle to enter Design Mode. Pressing the triangle
alternates
back and forth between Design Mode and Normal Mode
3) Press ICON with hand on top of form to get properties
4) Press Textbox to view properties
5) Check the (Name) property. Also make sure it is a TextBox by
looking
at
the first line of the property window.

"Krygim" wrote:

Hi Joel and Per,

Thanks for your response. I tried the following command in the
Immediate
window of Excel:
ActiveWorkbook.Sheets(1).TextBox1.Value="Some Value"

I got the error message: "Method or data member not found"
I have already named "Text Box 1" as "Textbox1"

But the following command was okay:
ActiveWorkbook.Sheets(1).Range("A1").Value="Some Value"

Any idea?

KM


"Joel" wrote in message
...
oExcel.ActiveWorkbook.Sheets(1).TextBox1.Value="So me Value"


"Krygim" wrote:

I insert a text box into Sheet1 of Excel and want to assign a value
to
this
text box via Excel automation from another program. I have tried
the
following syntax without success:
oExcel.ActiveWorkbook.Sheets(1).Textboxes("TextBox
1").Value="Some
Text"

What is the correct syntax (or object path) to refer to this text
box?

Thanks in advance.
KM

Reference to free-form textboxes
 

Should be activesheet (not activeworksheet)

This works
ActiveSheet.TextBox1.Text = "Some text"

also
ActiveSheet.OLEObjects("TextBox1").Object.Text = "Some text"

" wrote:

Hi Joel,

I tried in both Excel 2003 and 2007, the following one line statement
doesn't work.
ActiveWorksheet.Shapes("Text Box 1").Characters.Text="Some text"

After renaming the box to "Textbox1" and changing the statement accordingly
as follows, it still failed.
ActiveWorksheet.Shapes("Textbox1").Characters.Text ="Some text"

I have made sure no leading and trailing are in the name. It seems that 2
lines are necessary. Can you try in your Excel and confirm?

Thanks,
KM


"Joel" wrote in message
...
My instructions were for a text box like the one you are using. Most
people
don't realize you can get the properties of these boxes. It looks like
the
problem was with the spacing. Your latest code has a space between the
work
text and box. You would of seen the space by looking at the properties.

You can also do the same thing in one instruction

ActiveWorksheet.Shapes("Text Box 1").Characters.Text="Some text"

There is no need to select something and then perform an action. The
record
macro creates two instructions (select and action) where only one is
required. I like to take record macros and siomplify the code where it
makes
sense. Some instructions have to be done in two steps like a Paste
Special,
but most instructions do not.


"Krygim" wrote:

Hi Joel,

I am sorry that I didn't state my question clearly. I am talking about a
textbox floating over the cells of a normal worksheet rather than on a
UserForm.

I have just found a way to update its content as follows:
ActiveWorksheet.Shapes("Text Box 1").Select
Selection.Characters.Text="Some text"

My problem is solved. However I don't know if there is any simpler way.

Thanks again.
KM


"Joel" wrote in message
...
the question is if you really have a text box on the worksheett and
what
is
the real name. Text boxes havve both a caption and a name, you need to
check
what the name actually is. You can also change the name to anything
you
like.

You need to check the properties of the text box by doing the following
on
the worksheet and get into the "Design Mode"

1) Worksheet menu - View - Toolbars - Control Toolbox
2) Press triangle to enter Design Mode. Pressing the triangle
alternates
back and forth between Design Mode and Normal Mode
3) Press ICON with hand on top of form to get properties
4) Press Textbox to view properties
5) Check the (Name) property. Also make sure it is a TextBox by
looking
at
the first line of the property window.

"Krygim" wrote:

Hi Joel and Per,

Thanks for your response. I tried the following command in the
Immediate
window of Excel:
ActiveWorkbook.Sheets(1).TextBox1.Value="Some Value"

I got the error message: "Method or data member not found"
I have already named "Text Box 1" as "Textbox1"

But the following command was okay:
ActiveWorkbook.Sheets(1).Range("A1").Value="Some Value"

Any idea?

KM


"Joel" wrote in message
...
oExcel.ActiveWorkbook.Sheets(1).TextBox1.Value="So me Value"


"Krygim" wrote:

I insert a text box into Sheet1 of Excel and want to assign a value
to
this
text box via Excel automation from another program. I have tried
the
following syntax without success:
oExcel.ActiveWorkbook.Sheets(1).Textboxes("TextBox
1").Value="Some
Text"

What is the correct syntax (or object path) to refer to this text
box?

Thanks in advance.
KM

Reference to free-form textboxes
 

There are two textboxes you could use.

One is on the drawing toolbar and one is on the Control toolbox toolbar.

If you try adding a checkbox (manually) from each toolbar, you'll see that the
one from the drawing toolbar is named like: Text box 1 (with a spaces)

And the one from the control toolbox toolbar is named like: Textbox1 (no
spaces)

Option Explicit
Sub testme()

Dim xlApp As Application
Set xlApp = Excel.Application

'to change the value in the textbox from the control toolbox toolbar
xlApp.ActiveWorkbook.Worksheets(1).TextBox1.Value = "hi"

'to change the value in the textbox from the Drawing toolbar
xlApp.ActiveWorkbook.Worksheets(1).TextBoxes("text box 1").Caption = "bye"

End Sub


Krygim wrote:

I insert a text box into Sheet1 of Excel and want to assign a value to this
text box via Excel automation from another program. I have tried the
following syntax without success:
oExcel.ActiveWorkbook.Sheets(1).Textboxes("TextBox 1").Value="Some
Text"

What is the correct syntax (or object path) to refer to this text box?

Thanks in advance.
KM

--

Dave Peterson

Reference to free-form textboxes
 

Hi Dave,

Thanks for your syntax. It works like a charm!

KM


"Dave Peterson" wrote in message
...
There are two textboxes you could use.

One is on the drawing toolbar and one is on the Control toolbox toolbar.

If you try adding a checkbox (manually) from each toolbar, you'll see that
the
one from the drawing toolbar is named like: Text box 1 (with a spaces)

And the one from the control toolbox toolbar is named like: Textbox1 (no
spaces)

Option Explicit
Sub testme()

Dim xlApp As Application
Set xlApp = Excel.Application

'to change the value in the textbox from the control toolbox toolbar
xlApp.ActiveWorkbook.Worksheets(1).TextBox1.Value = "hi"

'to change the value in the textbox from the Drawing toolbar
xlApp.ActiveWorkbook.Worksheets(1).TextBoxes("text box 1").Caption =
"bye"

End Sub


Krygim wrote:

I insert a text box into Sheet1 of Excel and want to assign a value to
this
text box via Excel automation from another program. I have tried the
following syntax without success:
oExcel.ActiveWorkbook.Sheets(1).Textboxes("TextBox
1").Value="Some
Text"

What is the correct syntax (or object path) to refer to this text box?

Thanks in advance.
KM

--

Dave Peterson

Reference to free-form textboxes
 

Hi Joel,

I tried your two statements in Excel 2007. Both of them did not work.
However the syntax suggested by Dave works like a charm.

Thanks again.
KM


"Joel" wrote in message
...
Should be activesheet (not activeworksheet)

This works
ActiveSheet.TextBox1.Text = "Some text"

also
ActiveSheet.OLEObjects("TextBox1").Object.Text = "Some text"

" wrote:

Hi Joel,

I tried in both Excel 2003 and 2007, the following one line statement
doesn't work.
ActiveWorksheet.Shapes("Text Box 1").Characters.Text="Some text"

After renaming the box to "Textbox1" and changing the statement
accordingly
as follows, it still failed.
ActiveWorksheet.Shapes("Textbox1").Characters.Text ="Some text"

I have made sure no leading and trailing are in the name. It seems that 2
lines are necessary. Can you try in your Excel and confirm?

Thanks,
KM


"Joel" wrote in message
...
My instructions were for a text box like the one you are using. Most
people
don't realize you can get the properties of these boxes. It looks like
the
problem was with the spacing. Your latest code has a space between the
work
text and box. You would of seen the space by looking at the
properties.

You can also do the same thing in one instruction

ActiveWorksheet.Shapes("Text Box 1").Characters.Text="Some text"

There is no need to select something and then perform an action. The
record
macro creates two instructions (select and action) where only one is
required. I like to take record macros and siomplify the code where it
makes
sense. Some instructions have to be done in two steps like a Paste
Special,
but most instructions do not.


"Krygim" wrote:

Hi Joel,

I am sorry that I didn't state my question clearly. I am talking about
a
textbox floating over the cells of a normal worksheet rather than on a
UserForm.

I have just found a way to update its content as follows:
ActiveWorksheet.Shapes("Text Box 1").Select
Selection.Characters.Text="Some text"

My problem is solved. However I don't know if there is any simpler
way.

Thanks again.
KM


"Joel" wrote in message
...
the question is if you really have a text box on the worksheett and
what
is
the real name. Text boxes havve both a caption and a name, you need
to
check
what the name actually is. You can also change the name to anything
you
like.

You need to check the properties of the text box by doing the
following
on
the worksheet and get into the "Design Mode"

1) Worksheet menu - View - Toolbars - Control Toolbox
2) Press triangle to enter Design Mode. Pressing the triangle
alternates
back and forth between Design Mode and Normal Mode
3) Press ICON with hand on top of form to get properties
4) Press Textbox to view properties
5) Check the (Name) property. Also make sure it is a TextBox by
looking
at
the first line of the property window.

"Krygim" wrote:

Hi Joel and Per,

Thanks for your response. I tried the following command in the
Immediate
window of Excel:
ActiveWorkbook.Sheets(1).TextBox1.Value="Some Value"

I got the error message: "Method or data member not found"
I have already named "Text Box 1" as "Textbox1"

But the following command was okay:
ActiveWorkbook.Sheets(1).Range("A1").Value="Some Value"

Any idea?

KM


"Joel" wrote in message
...
oExcel.ActiveWorkbook.Sheets(1).TextBox1.Value="So me Value"


"Krygim" wrote:

I insert a text box into Sheet1 of Excel and want to assign a
value
to
this
text box via Excel automation from another program. I have tried
the
following syntax without success:
oExcel.ActiveWorkbook.Sheets(1).Textboxes("TextBox
1").Value="Some
Text"

What is the correct syntax (or object path) to refer to this
text
box?

Thanks in advance.
KM