CODEWITHSUNDEEP
excelvba
TenEM
TenEM

Reputation: 127

closing excel file as i close the userform without leaving other workbooks

how can i close an excel file as i close the userform without closing other excel files.

i used this code but it closses all the workbooks that are running.

Private Sub CommandButton1_Click()
   Unload UserForm1
   Workbooks("ThisWorkbook1").Close SaveChanges:=True
End Sub

How can i fix this code,

Thank you

Upvotes: 0

Views: 1241

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

Unload UserForm1

That's unloading the default instance of the form, which may or may not be the instance that's currently running. Avoid referring to UserFormModuleName in the form's code-behind: use Me instead:

Unload Me

That said, if you're planning to close the containing workbook just after that, whether you unload the form or not makes little to no difference whatsoever. In fact, I would avoid Unload altogether (see this article I wrote a little while back).

Assuming you mean to close ThisWorkbook (i.e. the code that contains this VBA code), then use ThisWorkbook to qualify the Workbook.Close member call:

ThisWorkbook.Close SaveChanges:=True

That said...

Workbooks("ThisWorkbook1").Close SaveChanges:=True

That instruction can only ever close one single workbook, if it doesn't blow up with error 9 because there is no ThisWorkbook1 workbook opened - your princess is in another castle, this cannot possibly close all opened workbooks.

I would look at the code that's invoking UserForm1.Show; if there's code after the .Show call that's doing something like Application.Quit, then that's why everything is closing: assuming you've shown us all of it, the form's code-behind can't possibly be doing that.

Upvotes: 1

Related Questions