Call UserForm procedures from another workbook

[Mats Samson]

Is it possible to Run a Userform.Show and call/run a procedure in the
Userform from another active workbook once the form is loaded but hidden.
I'm switching from the Userform to the "data" workbook, selects the data to
copy and press a button to copy & paste, switching back to the Userform and
continues to manipulate the data. I can do it with starting a procedure in a
module in the same workbook as the userform but I'd like to jump directly
back to the Userform.

 

[Bob Phillips]

You cannot access a class in another workbook, and a userform is just a
special type of class, add-in or not, directly. You have to get at it in a
roundabout way.

In the workbook with the userform create a standard code module procedure
for creating a form object and loading the form

Dim theForm As UserForm1

Sub LoadForm()
Set theForm = New UserForm1
End Sub

and another procedure to call into the form to invoke one of its methods
(make sure that the method is decalred as Public)

Sub CallIntoForm()
theForm.cmdButton_Click
End Sub


Then in the calling workbook, you invoke that routine like so


Application.Run "'The Userform Workbook.xls'!LoadForm"
Application.Run "'The Userform Workbook.xls'!CallIntoForm"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mats Samson]

I get compile errors. Dim…UserForm1 errors to “User-defined type not definedâ€
but I guess the 1 is the problem. But removing it I get “Invalid use of New
keyword†in the Loadform procedure instead!
Please observe that the UserForm is already loaded but hidden when working
in the other workbook. Excel seems to have a problem pasting data to cells
linked to the UserForm (textboxes), very often it starts calculating
endlessly. (Ctrl-Alt-Del is the only way out). I had an idea to reduce the
workload after the paste operation by reducing the number of steps getting
back and show the UserForm again.
Regards
Mats