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

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.

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

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

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

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