Set focus back to Excel after loading simulated custom form

Question

I have a UserForm that displays additional information based on the selection made on a worksheet.

The procedure that calls Userform is in the worksheet module, and the trigger is Worksheet_SelectionChange. A form is not needed, so the user can select another cell on the sheet. A form with data is displayed.

The problem is that Userform distracts attention from the Excel application, so the arrow keys do not work. And since the trigger is an event Worksheet_SelectionChange, it always takes focus after changing the selection.

How do I set focus back from a user form or prevent Userform from focusing first?

I looked at Application properties to restore focus and Userform properties to remove focus from Userform, but I can’t understand.

Answer 1

I would usually use:

AppActivate Application.Caption

Answer 2

Not a real solution, but a possible workaround that mimics show / hide without stealing focus more than once.

The idea is to show the Modeless user form and turn off the screen when the book is opened (before the user can do anything with it).

Then, when this one Worksheet_SelectionChangewith certain requirements is satisfied, move the Userform to the viewing screen ( UserForm1.ShowInScreen(LeftLocation)), otherwise cancel it on the screen ( UserForm1.GoOffScreen).

, Userform1 "" , ​​ B 1.

, , ( ).

ThisWorkbook

Private Sub Workbook_Open()
    With UserForm1
        .Show
        .GoOffScreen
    End With
End Sub

Sheet1 ( , )

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Target.Column = 2 Then
            With UserForm1
                If Not .Visible Then .Show
                .ShowInScreen Application.Left + Target.Width + Target.Offset(0, 1).Left
                .UpdateTextbox Target.Address(0, 0)
            End With
        Else
            UserForm1.GoOffScreen
        End If
    End If
End Sub

, Userform1:

Option Explicit

Sub UpdateTextbox(ByVal Text As String)
    Me.TextBox1.Value = Text
End Sub

Sub GoOffScreen()
    ' Assuming no screen above this: move above the screen 3 times the userform height
    With Me
        .Top = -3 * .Height
    End With
End Sub

Sub ShowInScreen(ByVal LeftLocation As Double)
    With Me
        .Top = Application.Top + 200
        .Left = LeftLocation
    End With
End Sub