UserForm.Show activates another workbook in Excel VBA

Question

I assign keyboard shortcut to open a VBA UserForm in Excel. For some reason when UserForm is shown Excel activates the workbook which was active the first time the UserForm was shown. The code is vanilla. Does anyone know why it happens and how to fix it?

Sub AssignShortcuts()
    Application.OnKey "^+%s", "UserFormShow"
End Sub

Sub UserFormShow()
    UserForm.Show
End Sub

Sub CloseButton_Click()
    Me.Hide
End Sub

I tried capturing and reactivating active workbook in UserFormShow(), but it didn't help. Debug.print outputs the right workbook, but it is not activated.

Sub UserFormShow()
    Dim wb As Workbook
    Set wb = ActiveWorkbook  
    frmMain.Show 
    wb.Activate
    Debug.Print "Activated Workbook: " & wb.Name
End Sub

Answer 1

When you do Me.Hide the form becomes invisible, but it remains "attached" to the workbook that was active when you opened the form. When you try to show the form again, it will indeed "reactivate" the workbook that was active when the form first appeared.

Demo 1:

• We initialize the form while Book1.xlsm is active.
• We hide the form.
• We open Book2.xlsm then Show the form again.
• Book1.xlsm appears behind the form :(

To fix that quickly you can simply use Unload instead of hiding the userform.

Sub CloseButton_Click()
    Unload Me
End Sub

Demo 2:

• Resuming after Demo 1, we Unload the userform.
• Activate Book2.xlsm then Show the userform.
• Book2.xlsm remains visible since the form got re-initialized :)
• Now, if we hide the form; open Book1.xlsm; and try to Show the userform from there, it's now Book2.xlsm that will appear behind the form.

The downside of this approach is that you have to re-initialize the userform everytime you what to display it. If this is a costly operation, there is an alternative approach, but you have to make Windows API calls to change the ordering of the windows which might be overkill for your needs. However, I can always add something about this to my answer if need be.