Closing a userform that is in workbook A from workbook B

Question

I'm new to VBA so there might be a simple answer to this question but if there is I sure haven't found it. What I am doing is copying data from several workbooks into one master workbook. I have writen the code for this and it works fine. The only problem is the workbooks where I'm retriving the data have userforms that automatically initiate when the workbook is accesed. This means that when I run my code to copy the data it hangs at each userform and wont continue until I've physically closed each userform. So my question is: Is there a way to remotely close the userforms in the raw data workbooks from my master workbook VBA code? Thanks in advance.

Answer 1

Assuming you mean that the forms pop up when you open the workbooks, disable events before doing so:

Application.Enableevents = False
Workbooks.Open ...
Application.Enableevents = True

for example.

Answer 2

I would suggest trying

Application.EnableEvents = False

Further reading.

Short description: All events (Workbook_Open, Workbook_BeforeSave etc), that usually fires upon opening or closing a workbook, will be ignored.

I have written the following functions to make all macros a bit simpler (and faster). Simply place these functions in a regular module.

Public Function CalcOff()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
End Function
Public Function CalcOn()
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Function

Begin your macro with:

CalcOff

And then end your macro with:

CalcOn

Remember that you need to put "CalcOn" in all places that exits the running macro.

Disabling ScreenUpdating makes the code "run in background" (nothing will be displayed).

Setting Calculation to manual improves the speed of the code, since no calculations will be made when changing data. But it's very important to exit all macros with "CalcOn", otherwise your sheet won't calculate (and that's not funny), and it will look like Excel has frozen (since ScreenUpdating would still be turned off).

However, if you by any chance happen to break a running code without exiting it the proper way (running "CalcOn"), simply close the Excel application and reopen it. Or run a macro that ends with the "CalcOn" code. Or create a new macro with that simple line.

Answer 3

to close all userforms, (if you want a specific one , change my code)

sub Close_Userforms()
Dim Form as VBA.Userform 'if not work change to Object
For each Form in VBA.Userform
    'can add a condition, like :  if Form.name ="Whatever" then
    unload Form 'if you don't want to lose the data from the userforms, Form.Hide, and later re-loop and Form.Show
next Form

edit : can also if Typename (Form)="Whatever" then , for the condition