Reference to free-form textboxes

  • Thread starter Thread starter Krygim
  • Start date Start date
K

Krygim

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
 
Hi

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

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

Regards,

Per
 
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
 
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.
 
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
 
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.
 
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
 
Should be activesheet (not activeworksheet)

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

also
ActiveSheet.OLEObjects("TextBox1").Object.Text = "Some text"
 
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
 
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
 
Back
Top