Force a form to close in a new opened worksheet

[Islwyn]

Hi, I hope someone can help.

I have a workbook (Book1.xls) which, by using a button on a form, opens a second workbook (Book2.xls) and copies data from it into the original (Book1.xls).

The second workbook automatically opens a form when it is opened, and this is bringing the process to a halt before I get to the bit where I want to close Book2.xls. The code works fine but you have to manually close the form in the newly opened workbook before the process completes. Because the code will ultimately be used for duplicating more than one workbook this is not desirable - I want the process to complete without the user having to manually close down the forms which pop up every time a new file is opened ready for copying.

Can anyone tell me how to select the open form in Book2.xls and force its closure so the code continues to run without the user having to manually close each form.

Thanks in advance for any responses

Here is the code I have tried using which does NOT work:

Please Login or Register  to view this content.

[AlvaroSiza]

The second workbook automatically opens a form when it is opened, and this is bringing the process to a halt before I get to the bit where I want to close Book2.xls.

This feels like the form is being called under the Workbook_Open event within the ThisWorkbook sheet object. I would either add conditional language to determine if you would indeed like it opened, or just delete it and manage its call outside of this event.

Hope this helps,

-as-

[rvasquez]

Hello there,

In the OldWorkBook you could place the following code which determines if the NewWorkBook is open and if it is then don't show form, if it's not the show userform on open.

Please Login or Register  to view this content.

Code found at

http://www.exceltip.com/st/Determine_if_a_workbook_is_already_open_using_VBA_in_Microsoft_Excel/472.html

[Leith Ross]

Hello Islwyn,

The code halts because the UserForm being shown is Modal. This means that the application program hosting the UserForm will not accept input until the UserForm is closed. You can change this default property setting using the Properties window. Change the ShowModal property to False and save your changes. Next time, the UserForm will be displayed but allow the VBA to continue.

[Islwyn]

Thanks for all your replies. I should have said that I did look at changing the modal option but was keen to avoid this as I want to lock users into using single forms. rvasquez's WorkbookOpen function looks ideal so I will give it a try.

Cheers

Is