UserForm Issue

[Thomas]

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

 

[Mark]

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.

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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]

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.