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
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:
To fix that quickly you can simply use Unload
instead of hiding the userform.
Sub CloseButton_Click()
Unload Me
End Sub
Demo 2:
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.