Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issue
Hi,
I am opening an excel spreadsheet with a userform from another application (see code below) using late binding. I need to populate controls with data. How do I reference the userform? Set objExcel = CreateObject("Excel.Application") Set wbExcel = objExcel.Workbooks.Open("C:\test.xls") I tried wbExcel.UserForm11.TextBox1.Text and it does not work. Any ideas? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issue
I am not sure of the exact syntax, but I do know that the userform object IS NOT a member of excel. It is a member of the VBAProject Object. You might try using the Object Browser in the VBE to trace out the correct relationships. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issue
To show the userform, you have to run code contained in the workbook that
contains the userform. You can have that code return a reference to the userform to your code and then use that to address the userform. -- Regards, Tom Ogilvy "Thomas" wrote: Hi, I am opening an excel spreadsheet with a userform from another application (see code below) using late binding. I need to populate controls with data. How do I reference the userform? Set objExcel = CreateObject("Excel.Application") Set wbExcel = objExcel.Workbooks.Open("C:\test.xls") I tried wbExcel.UserForm11.TextBox1.Text and it does not work. Any ideas? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issue
Just some added info:
Here is some sample code from Rob Bovey: UserForms in an Excel project are private classes, so you can't call a UserForm in another project directly. What you can do is create a public function in the other project that returns an object reference to the UserForm. You will then be able to control the UserForm with this object reference from the outside project. For instance, if you have a UserForm named frmMyForm in the workbook Server.xls, you can add the following function to a regular module in that workbook to expose the UserForm Function ReturnForm() As frmMyForm Set ReturnForm = New frmMyForm End Function Then you can show this UserForm from a different procedure like so: Sub ShowForm() Dim objForm As Object Set objForm = Application.Run("Server.xls!ReturnForm") objForm.Show End Sub Rob Bovey -- Regards, Tom Ogilvy "Thomas" wrote: Hi, I am opening an excel spreadsheet with a userform from another application (see code below) using late binding. I need to populate controls with data. How do I reference the userform? Set objExcel = CreateObject("Excel.Application") Set wbExcel = objExcel.Workbooks.Open("C:\test.xls") I tried wbExcel.UserForm11.TextBox1.Text and it does not work. Any ideas? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issue
Userforms are not part of any collection until they are loaded, so you need
to load them then refence it. Load UserForm1 UserForms.Item(0).TextBox1.Text = "ABC" -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thomas" wrote in message ... Hi, I am opening an excel spreadsheet with a userform from another application (see code below) using late binding. I need to populate controls with data. How do I reference the userform? Set objExcel = CreateObject("Excel.Application") Set wbExcel = objExcel.Workbooks.Open("C:\test.xls") I tried wbExcel.UserForm11.TextBox1.Text and it does not work. Any ideas? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Issue
Thanks for the post. After posting I figured out that I could build a
public method in the workbook that I can pass data and that method interact with the userform. "Tom Ogilvy" wrote in message ... Just some added info: Here is some sample code from Rob Bovey: UserForms in an Excel project are private classes, so you can't call a UserForm in another project directly. What you can do is create a public function in the other project that returns an object reference to the UserForm. You will then be able to control the UserForm with this object reference from the outside project. For instance, if you have a UserForm named frmMyForm in the workbook Server.xls, you can add the following function to a regular module in that workbook to expose the UserForm Function ReturnForm() As frmMyForm Set ReturnForm = New frmMyForm End Function Then you can show this UserForm from a different procedure like so: Sub ShowForm() Dim objForm As Object Set objForm = Application.Run("Server.xls!ReturnForm") objForm.Show End Sub Rob Bovey -- Regards, Tom Ogilvy "Thomas" wrote: Hi, I am opening an excel spreadsheet with a userform from another application (see code below) using late binding. I need to populate controls with data. How do I reference the userform? Set objExcel = CreateObject("Excel.Application") Set wbExcel = objExcel.Workbooks.Open("C:\test.xls") I tried wbExcel.UserForm11.TextBox1.Text and it does not work. Any ideas? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform date issue | Excel Worksheet Functions | |||
UserForm Save Issue | Excel Worksheet Functions | |||
Multiple Users in Userform issue | Excel Programming | |||
Userform Issue | Excel Programming | |||
UserForm Listbox issue | Excel Discussion (Misc queries) |