I have an Excel add-in running a procedure that displays an OKOnly MsgBox if a certain criteria is not met, and attempts to close a userform, activate a specific workbook, and terminate code execution. In the UserForm_Terminate() event I have:
Private Sub UserForm_Terminate()
Debug.Print ActiveWorkbook.Name
Application.ScreenUpdating = True
wbk.Activate
sht.Activate
Debug.Print ActiveWorkbook.Name
End
End Sub
If I begin running the procedure with a new blank workbook active, that workbook is still the active workbook when code terminates, but both print statements above indicate that the target wbk is actually active. However if I use a breakpoint and step through wbk.Activate, the target wbk is activated as expected. The workbook and worksheet objects are both available and there is no error. Any ideas why Workbook.Activate is not behaving as expected during execution? I expected turning screenupdating on would solve my issue but no dice.
I'm not sure if the SDI bug is my issue but it did lead me to an answer (thanks @Profex). I was not able to reproduce that bug, and there is also another path in my procedure that ends with the same Userform_Terminate() event and does reactivate the target wbk, though I can't determine what is causing the differing functionality. Nonetheless, one solution to that issue was to hide and then show the active window and that suffices in this case, though probably not ideal in many situations:
Private Sub UserForm_Terminate()
Dim win As Window
wbk.Activate
sht.Activate
Set win = Application.ActiveWindow
win.visible = False
win.visible = True
End
End Sub