Prevent UserForm from being visible when called from another macro in VBA

Question

I am running a macro from one workbook (ie. wb1.xlsm) that copies a template workbook into another location and renames it (ie. wb2.xlsm). It then uses Application.Run to run an AutoSetup() Sub located in wb2. This creates the appropriate sheets based on the parameters its given.

My problem occurs during this process. It is set up using existing functions which occur in a UserForm.

When I run the macro I do not want to see anything pop up, yet even with Application.Events and Application.Visible set to False The UserForm that performs the setup calculation still pops up and is visible.

Any suggestions?

Code below:

'AutoSetup Module
Public Sub AutoSetup(Project As String, Program As String, TestName As String, _
                     TestType As String, TaskNumber As String, Token As String)
    Dim TokenArr() As String

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set IntSht = ActiveWorkbook.Sheets("Integrations")
    Set DctSht = ActiveWorkbook.Sheets("Duct")
    IntSht.Range("B4").value = Program
    IntSht.Range("B5").value = TestName
    IntSht.Range("E4").value = Project
    IntSht.Range("E5").value = TaskNumber
    Call WorkbookSetup
    MenuForm.TestSetBox.value = TestType
    TokenArr = Split(Base64DecodeString(Token), ",")
    EPFLogin.TextBox1.value = TokenArr(0)
    EPFLogin.TextBox2.value = TokenArr(1)
    MenuForm.LoadSheets (True)
    DctSht.Activate
    ThisWorkbook.Save
    ThisWorkbook.Close
End Sub

Why is there a userform ? Does it allow for some change in the calculation ? Can you post your code for AutoSetup(), so we can see what's happening ? — Mitch
Do you have the rights (technically and in your organization) to update the template? — Dirk Horsten
The UserForm (MenuForm) is used for Manual Setup. Essentially I'm automating the process of the manual setup by sending the parameters a user would enter and then simulating the click event. — Steven Good
That I understood, and I assume you have to preserve the functionality of the original application to allow manual setup occasionaly, but are you allowed to change the code? — Dirk Horsten

Dirk Horsten Dirk Horsten · Accepted Answer · 2017-08-13T13:45:17

Within wb2.xlsm, move the calculations to a separate Subroutine in a separate module. Call this Subroutine from 'AutoSetup' after showing the UserForm.

Then from wb1.xlsm, call the new subroutine.

Prevent UserForm from being visible when called from another macro in VBA

1 Answers