Excel 2013 modal VBA Userform changes Activeworkbook while userform is visible

Question

In Excel 2013 (Office 15) a combination of a shortcut and a very simple userform (for formatting) is sometimes causing the Active Workbook to disappear and another workbook to show for no reason (only happens sometimes, often after just having changed activeworkbook). I am trying to keep the current workbook window in the foreground while the formatting userform is shown but without success.

Here is my code:

Sub AssignKeys()
 Application.OnKey "^%,", "MakeBlue"
End Sub


Sub MakeBlue()
 frmFormat.Show     
End Sub

frmFormat is a very simple and small form that only has one textbox where a formatting code is entered. It does work to restore the previously active workbook after hiding the modal form using this code:

Private Declare PtrSafe Function SetForegroundWindow Lib "USER32" (ByVal hWnd As LongPtr) As LongPtr
Public Sub MyAppActive(Handle As Long)
    Dim lngStatus As LongPtr
   lngStatus = SetForegroundWindow(Handle)
End Sub

Private Sub tbShortcut_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)


If tbShortcut.Text <> "xn" Then
    Dim mySel As Range
    Dim myWb As workBook
    Dim myWnd As Window
    Set myWnd = ActiveWindow

    Set mySel = Selection
    Set myWb = ActiveWorkbook
    Debug.Print mySel.Address

    Me.Hide

    'mySel.Parent.Parent.Activate
    myWb.Activate
    myWnd.Activate
    MyAppActive myWnd.hWnd

end if

end sub

So once the form is hidden, the original workbook window is restored. But while the userform is shown, the other unwanted Workbook window is displayed. Would be very greatful for any hints. Thank you!

If you don't want the active view to change, why are you calling .Activate? — Comintern
If I do not call activate at the end, then after closing the userform I will not be back at the original workbook. By using activate in the end, I can at least get back to the original workbook after the modal userform closes. — Joe Bo

Tuntable Tuntable · Accepted Answer · 2019-10-26T02:23:02

The following seems to work most of the time. The OnTime seems to be necessary. I suspect the Microsoft bug is with the ribbon.

fsShow myUserForm

Sub fsShow(form As Object)
' Work around bug that form.show changes active window rather randomly
' (Only the displayed window is wrong, not the ActiveCell.parent.parent.)
  Set fsActiveWindow = Application.ActiveWindow
  form.Show
  Application.OnTime Now(), "fsResetActiveWindowOnTime"
End Sub

Sub fsResetActiveWindowOnTime()
  If Not fsActiveWindow Is Nothing Then
    fsActiveWindow.Activate
    Set fsActiveWindow = Nothing
  End If
End Sub

However, I occasionally get a type mismatch error just by calling this, i.e. a userform is not an object(!). This whole area is buggy so I avoid using this method.

Sometimes just recreating a userform fixes the problem. It is not for all userforms. And I have not figured out a trigger.

Excel 2013 modal VBA Userform changes Activeworkbook while userform is visible

1 Answers