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
– Mitch, Commented Aug 13, 2017 at 12:04
Do you have the rights (technically and in your organization) to update the template? — Dirk Horsten
– Dirk Horsten, Commented Aug 13, 2017 at 13:33
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
– Steven Good, Commented Aug 13, 2017 at 13:33
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, Commented Aug 13, 2017 at 13:37

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

1

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.

answered Aug 13, 2017 at 13:45

Dirk Horsten

3,8554 gold badges22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steven Good Over a year ago

I want to avoid showing the UserForm at all during AutoSetup. So your suggestion would be to perform all subroutines from a module instead of a form. This is kind of what I assumed, but i was hoping that wasn't the case.

YowE3K Over a year ago

@StevenGood The suggestion is to move the part of the code that is doing the calculations out of the UserForm to a spot that is more sensible. Then the UserForm (when it is displayed) can call those calculations (passing any parameters necessary), and your macro (without having to display the UserForm) can also call those calculations (passing any parameters necessary). It is usually best if a form's code only includes the code related to the form itself.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related