excelvbams-office

UserForm.Show activates another workbook in Excel VBA


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

Solution

  • 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:

    Showing how showing a previously hidden userform bring with it the old active workbook

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

    Sub CloseButton_Click()
        Unload Me
    End Sub
    

    Demo 2:

    enter image description here

    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.