CODEWITHSUNDEEP
vbaexceluserform
CiViCChiC79
CiViCChiC79

Reputation: 31

Why does Macro Enabled Excel Workbook kill UserForm in another Macro Enabled Excel Workbook when closed?

Scenario: User is running a macro-enabled Excel Workbook. User clicks a link on a UserForm that launches another macro-enabled Excel Workbook that also has user forms. The second Workbook is then closed using the following code:

Private Sub btnExit_Click()

'Check if other Excel files are open before quitting the application
If Workbooks.Count = 1 Then
    Application.Quit
Else
    ThisWorkbook.Close
End If

End Sub

and...

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim intResponse As Integer

If MsgBox("Are you sure you would like to exit the calculator?", vbYesNo + vbQuestion) = vbNo Then
    Cancel = True
End If

ThisWorkbook.Saved = True

End Sub

Upon closing of the second Workbook (ThisWorkbook.Close), the original Workbook remains open, however, the vbModeless UserForm that was previously visible has been killed. It might also be pertinent to mention that the second Workbook's forms are Modal.

If the second Workbook is closed using the Excel exit button in the upper right corner, this problem does not occur.

Does anyone know why this is happening? Is it because code execution for Excel.Application is ceased when the Workbook is closed programmatically? Could it be due to the difference in form modes? Is there a work-around other than putting a button on the original Workbook in a Sheet to re-launch the UserForm?

Many thanks for any assistance with this issue!! CiViCChiC79

Upvotes: 1

Views: 182

Answers (2)

CiViCChiC79
CiViCChiC79

Reputation: 31

Changing the second Workbook's form containing the Exit button to vbModeless fixed the problem!!!! Yay!!!!!

Upvotes: -1

Vityata
Vityata

Reputation: 43585

This is what is happening:

  • Assuming that both workbooks are in the same Excel Application, Application.Quit would close them both.

  • ThisWorkbook.Close will close the workbook, in which the code is present.

Thus, concerning your question - you are not closing the second workbook with ThisWorkbook.Close, but you are closing the workbook, which has the btnExit button, on which you have clicked. I guess that both the workbooks are quite similar and you are mistaken which one you are closing?

Upvotes: 1

Related Questions