Reference Object on User Form in separate Workbook

[Maryz]

I know how to run a macro that resides in a module in another workbook.

Application.Run "'MacroSource.xls'!Macro1"

What I would like to do is control an object that resides on a user form in another workbook and I’m having problems with the statement. For example, I like to have a user press a command button on UserForm1 which resides in Book1.xls and change the caption on Label1 of the same form with Macro1 from a module of another book called MacroSource.xls. The statement above will allow me to execute Macro1 from Book1.xls. Where I need help is the Macro1 procedure.

Sub Macro1()
Book1.xls UserForm1.Label1 = "Hello"
End Sub

How do I properly combine the above statement to control Label1 on UserForm1 in Book1.xls from Macro1 which resides in a module in MacroSource.xls?

 

[Ken Puls]

Hi there,

I must admit that I had to read your question about 10 times, and I'm still not sure I've quite got what you're after.

You are trying to control a label's caption from a different workbook?

One thing to be aware of with userforms is that (to my knowledge) you cannot change the defaults via code (from any workbook), only by manual action. You can change it at runtime, but I believe that the userform needs to be loaded to do this.

Have you tried loading the userform before adjusting the caption?

Assuming you are calling Macro1 from another workbook (using application.run), and Macro1 exists in the target workbook, I would try (untested) something like:

sub macro1()
load userform1
userform1.show
end sub

and in the userfom_initialize event, try changing the label there.

HTH,

 

[Maryz]

Ken,

Let me clarify.

2 workbooks
1 user form
Both workbooks are open at the same time
The user is loaded and shown

The user executes a macro from the user form while the form is shown (click a command button or select a name from a list box, etc.). Let’s call the procedure RunMe().

The executed procedure has another procedure embedded in it. The embedded procedure is in a module in another workbook. For example:

Sub RunMe()
Msgbox (“Go to MacroSource.xls workbook and run the macro called Macro1”)
    Application.Run "'MacroSource.xls'!Macro1"          'runs the procedure whose code resides in a different workbook than this one
End sub

Skip over to the macro called “Macro1” in the MacroSource.xls workbook. This is the macro that is executed as part of the procedure above.

This is where I am having a problem properly writing the code for Macro1. Let’s say that Macro1 takes a value from the user form (name from a list box) and then wants to display a caption on a label on the user form (remember there is only one user form and it is NOT in the same workbook as Macro1 even though the form is shown on the screen).

Sub Macro1()
With Worksheets("Sheet1").Range("Names")
iFoundName = .Find(What:=UserForm1.ListBox1, After:=.Cells(1, 1), LookIn:=xlValues, LookAt _
       :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
       False).Row
End With

UserForm1.Label1.Caption = “The desired name is in row “ & iFoundName
End Sub

In the Macro1 procedure above, I need to include a reference to the other book (the one with the user form) for both UserForm1.ListBox1 AND UserForm1.Label1.Caption. This is where I need help. Hopefully this was clearer.

 

[Ken Puls]

Hi Maryz,

Much clearer, thanks. I'm not sure I'll be able to help you with this... I think I'd be doing as much experimenting as you.

A couple of options to think about maybe:

1) Converting your Macro1 to a function. This would mean that you could run it to return a value to your procedure. At that point, you'd run the function from your original workbook.

2) Using a public (& static?) variable. In your Macro1, you could assign the desired value to this variable, and then return to your originating code to make use of it. Just be aware that you'll want to set that variable to empty before you completely exit the subroutines.

For the record though, I have never tried one of these across workbook, but I can't see why it couldn't be made to work.

Sorry I can't be of more help here, but hopefully one of these is a new direction and will get you off and running. (Or maybe someone else can post to give you a better answer.)

Cheers,

 

[Andrew Poulsom]

Try using the Designer object like this:

Sub Test() 
    Dim VBC As Object ' UserForm VBComponent 
    Set VBC = Workbook("Book1.xls").VBProject.VBComponents("UserForm1") 
   VBC.Designer.Controls("Label1").Caption = “The desired name is in row “ & iFoundName 
End Sub

 

[Juan Pablo González]

I wouldn't use the VBProject way, just because you will possibly run into a HUGE pile of problems if you're using Excel XP or 2003.

So, what I did is this. Created two books, Book2.xls and Book3.xls.

Book3 has the Userform1, and Book2 has the "Macro1" sub that you're calling.

In the userform module I put this:

Private Sub CommandButton1_Click() 
    MsgBox ("Go to MacroSource.xls workbook and run the macro called Macro1") 
    Application.Run "'Book2.xls'!Macro1" 'runs the procedure whose code resides in a different workbook than this 
End Sub

Now, back in Book2, I added a module, with this code:

Sub Macro1() 
Dim UF As Object 
    Set UF = Book3.GetUserform1() 
    UF.Caption = "Testing..." 
End Sub

If you see, I'm using a function (not yet written) from Book3. For that, we need to do one thing first. Select the Book3, go to Tools | References, and change the VBAProject text to something unique (Book3 in my case...). Now, back to Book2, go to Tools | References, put a check next to 'Book3' to create a reference (which means that we can call the public subs and functions from that book as if they were in Book2).

Finally, we need to create the function that returns the reference to the Userform in Book3. So add a module there, and put this code:

Function GetUserform1() As UserForm1 
    Set GetUserform1 = UserForm1 
End Function

and that's it !

 

[Maryz]

Juan Pablo,

Sorry. I’ve been away for a couple of days. I completely understand your solution. The problem is that the paired workbooks will be available to multiple users on various desktops, so creating a reference from one book to the other through the VBE is not an option (as most users have no idea what a VBE is). If the reference could be made through VBA, then it would be perfect, but I’m pretty sure this cannot be accomplished. Is there any way to accomplish the same task without the manual reference?

 

[Juan Pablo González]

Well, I don't know... maybe using Application.Run(), I mean, I've never tried it, but something like

Set UF = Application.Run("'Book3.xls'!GetUserform1")

see if that works...

 

[Maryz]

Juan Pablo,

That worked perfectly. Thank you.

 

[Juan Pablo González]

Cool !