Wednesday, March 21, 2018

vba - 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

No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...