unable to close opened excel workbook

3
votes

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.

6
vbaexcel
I don't see any code where you try and close a workbook here...... - Sorceri
I am talking about closing through clicking the cross. I am opening workbooks with information and after someone has seen the information they need they want to close the workbook again. however with my code you need to switch between workbooks before you can click on the cross - Mick17
I have no idea what would cause this, but you know that workbooks.Open method sets a new active workbook, so your wb.Activate statement isn't doing anything extra. Insert ThisWorkbook.Activate right before the wb.Activate and see if that hacks the solution. - David Zemens
Also not working. What I also find strange is when i open a file directly by clicking on an button on the sheet i don't have this problem. when i open a file by clicking a userformbutton i get this malfunction. - Mick17
I think the Unload statement should come after the others, though that may nto solve the problem it is probably best to do it that way. Are you able to do workbooks.close("file") via the VBA code? Or does that present any error or failure? If so, that might provide a clue to the root cause of your problem. - David Zemens

6 Answers

2
votes

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.

0
votes

Unload only affects a UserForm.

Try adding a reference to the open workbook and then closing it like so:

dim wb as Workbook
set wb = Workbooks.Open "pathname"
wb.activate
' do whatever with it while it's open
wb.Close

If whatever you are doing is not automated, you will need a button on your form that will set the reference to the workbook and close it once the user has completed editing.

0
votes

If you define the workbooks that you open as a variable - you will be able to close them easily. Keep in mind this does not save the workbook. 

Dim DataBook As Workbook
Set DataBook = Workbooks.open ("pathname")
' Do something useful with the file

DataBook.Close
0
votes

It is not completely clear what you are trying to ask here, but I'll give it my best in providing you a full answer. If you are having no success with the Unload Me statement when it comes to closing a user form, try specifying the full form name in VBA. That is:

Unload UserFormName

If you are trying to close the workbook you have opened (via the user form), you can use the Workbooks.Close method:

Dim wb as Workbook
Set wb = Workbooks.Open(Filename:="C:\example.xls") 
wb.Activate
'Close workbook
wb.Close
'Unload form
Unload ExampleForm

More on the use of the Unload statement can be found here:- https://msdn.microsoft.com/en-us/library/aa445829(v=vs.60).aspx

And more on the use of the Workbooks.Close statement can be found here:- https://msdn.microsoft.com/en-us/library/office/ff838613.aspx

0
votes

I'm not sure what's causing the behavior you describe (inability to click the "Close" X button in the active Excel window). However your current workflow is:

  1. User interacts with form to review/access a file
  2. User clicks a button on the form which unloads the form
  3. User is then expected to manually close out of the file opened in (1)

A better solution would be to remove the third step, and close the workbook from one of the form's event-handlers (UserForm_QueryClose or UserForm_Terminate). Alternatively you could add this to a CommandButton's Click event, but that's not very different than requiring the user to manually close the file.

This could be as simple as:

Private Sub UserForm_Terminate()
' should be called when form Unloads
On Error Resume Next
Workbooks.Close "filename.xlsx"
End Sub

In this manner, the file will be closed any time the user closes or otherwise unloads the userform.

0
votes

OK I think I found a way around it.

Let's say I have a workbook1 which has a form called ControlPage with command button that opens up workbook2.

Private Sub CommandButton1_Click()

Dim workbook2 As Workbook
Dim workbook1name As String
workbook1name = ThisWorkbook.name

Set workbook2 = Workbooks.Open("workbook2.xlsx")
ControlPage.Hide
Workbooks(workbook1name).Activate
Workbooks("workbook2.xlsx").Activate

End Sub

So basically because 'activate' bit doesn't seem to work properly when I try to activate workbook 2 straight away, I just activate the first, and then the second one ane after another. Twisted but did the trick for me.