0

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.

braX
  • 11,506
  • 5
  • 20
  • 33
Ric
  • 1
  • 2
  • What version of Excel, 2013? – Profex Jun 12 '18 at 19:26
  • I don't recommend ever relying on `ActiveWorkbook` - Instead, fully qualify each workbook reference and then you won't have these problems. – braX Jun 12 '18 at 19:31
  • 1
    Add a `DoEvents` line after your `Activate` statements – dbmitch Jun 12 '18 at 19:33
  • 1
    Version 2013. @braX - ActiveWorkbook is just used here to debug, as I am truly trying to switch the active workbook to show the user. wbk and sht are fully qualified references to the objects I'm trying to activate. – Ric Jun 12 '18 at 19:35

2 Answers2

1

@barX He's not relying on ActiceWorkbook for code, he's just checking it to see if it's working ....

BTW Welcome to the Excel 2013 SDI bug!

Maybe following the proper way of initializing/using a Userform will help. See Mathieu's RubberDuck article on Userform.Show

On a related note, maybe changing the Parent Window Handle for the form before closing might work as well. See Keeping Userforms On Top Of SDI Windows In Excel 2013 And Up

Profex
  • 1,370
  • 8
  • 20
0

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
Ric
  • 1
  • 2