Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference Tab name to two textboxes | Excel Discussion (Misc queries) | |||
Clear all textboxes on a form ? | Excel Programming | |||
Find textboxes on a form ? | Excel Programming | |||
TextBoxes on a Form | Excel Programming | |||
Form Textboxes | Excel Programming |