unable to close opened excel workbook

Question

I am building a excel file with commandbuttons, userforms etc. that contain links to other workbooks.

For the commandbuttons on my sheet I use the same code as for the commandbuttons in my userforms:

workbooks.open "path"

with the userform commandbuttons ater this the following is added

unload me

When I open a workbook via a userform I am unable to close it afterwards. I must activate my workbook first, then activate the opened one and then can I close it

I have tried putting "unload me" befor and after the "workbooks.open" but this doesn't change anything.

I also tried the followin, also didn't work:

unload me
dim wb as workbook
set wb = workbooks.open"pathname"
wb.activate

anyone any ideas?

Example of how it is now: Someone needs to make a price calculation. they open the prices userform in my file. they click on the button "calculationfile". The calculationfile opens. they make there calculation and now they are finished in the calculationfile. So they want to close it by clicking on the cross. But they can't click the cross. then they switch to my file on the taskbar and then switch back to the calculation file. now they are able of clicking the cross

I dont understand why they can't click it the first time but they can click it after switching between mine and the openend workbook.

Answer 1

I suspect this is due to improper form handling and the "default instance" recreating itself after you unload it. If you don't create your own instance of the form before you show it, VBA will do all kinds of squirrelly things when you use it after it's unloaded.

If all you need to do is open a workbook and unload the form, *don't increment the workbook's reference count before you unload the form. Also, don't attempt to run any other code after you call Unload Me from the form. The code you posted should simply be:

Workbooks.Open "pathname"
Unload Me

Of course the calling code for the form isn't in the question, but it can likely be solved there as well.