How do I switch focus from a modeless user form back to the excel.

[Guest]

When I call a modeless user form, I want to then switch the focus back to the
excel window that was active when the form was called, but leave the form
active (but not with the focus). Nothing I've tried has worked so far.

 

[Tom Ogilvy]

Sub Tester3()
Mainform.Show vbModeless
With Mainform 'the modeless userform
.Height = 32
.Width = 40
.Left = 30
.Top = 30
End With

AppActivate Application.Caption

End Sub
Sub Tester4()
With Mainform 'the modeless userform
.Height = 32
.Width = 40
.Left = 30
.Top = 30
End With

AppActivate Application.Caption
End Sub

both worked fine for me.

 

[Guest]

Thanks Tom...that did it for me.

Anoter possibility is to use appactivate:

An example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$3" Then
UserForm1.Show vbModeless
AppActivate Application.Caption
End If
End Sub

 

[Guest]

Thanks RB...Tom's solution worked in my case...but I'll hold onto this in
case it fails some day.

 

[RB Smissaert]

It doesn't work with me when I call the Sub from a keyboard shortcut.

RBS