Working with Userforms from another workbook

Question

I have an add-in that is protected by my company. My goal is to write some VBA to automate much of the clicking around the add-in. Much of the code in there revolves around what is entered in to userforms so the method I am trying now is to write code to work with those userforms instead of rewriting their code to not use the userforms.

The add-in has a macro in one of the modules that opens the userforms. So I just use that to open the userform, which is not a problem. But how do I access the functions within the userforms? I need to do that to simulate the clicking on the userform.

Not sure what that is. I have tried WorkbookName.FormName.FunctionName — Horace
That will only work on public functions though, so may not have commandbutton code exposed. Get the form object, MSDN that, then get the controls, then you may be able to call the click from that. — Nathan_Sav
I see. What do you mean by MSDN that? If I search for msdn every webpage by microsoft has msdn in the URL but doesn't say much about it — Horace
No, you use the MSDN web site, searching for form, or just google form object. msdn.microsoft.com/en-us/library/office/gg264663.aspx — Nathan_Sav
well in the immediate window I am able to run this workbook_name.form_name.button_name.Value = True which clicks the button. But when I run it within my Sub I get the error "Method or data member not found", and it highlights the userform_name — Horace

Scarfe Scarfe · Accepted Answer · 2016-12-20T15:19:14

You can access functions within a user form using the format you have mentioned previously in your comments above. For the sake of providing a complete answer, that is:-

WorkbookName.FormName.FunctionName

However, for this to work the function of concern must be declared as public from within its own code. An example:

Public Function ExampleF(Param1 As Integer)
' Function code
End Function

If you then wanted to set Param1 from outside of your user form, you could use the format FormName.ExampleF(123).

This would set Param1 = 123, and your function could continue to use this value for its code.

Unfortunately if the user form that you are trying to access has not declared its functions as public, I'm afraid they are only accessible by the form itself and there is not much else you can do.

Your best hope would be to attempt to rewrite the add-in completely, or ask that it is unprotected for the purpose of making the functions public.

Working with Userforms from another workbook

1 Answers