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

[Yoam69]

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.

 

[RB Smissaert]

I had the same trouble and the only thing that worked was this:

Private Declare Sub mouse_event Lib "user32" _
(ByVal dwflags As Long, _
ByVal dx As Long, _
ByVal dy As Long, _
ByVal cButtons As Long, _
ByVal dwExtraInfo As Long)

Private Const MOUSEEVENTF_LEFTDOWN = &H2
Private Const MOUSEEVENTF_ABSOLUTE = &H8000


Sub SendMouseLeftClick(ByVal lX As Long, ByVal lY As Long)
'NOTE: lX and lY are assumed to be Screen coordinates
' relative to the uper left corner (0,0).
'''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lFlags As Long

'Set cursor position
SetCursorPos lX, lY

'Send the mouse event
lFlags = MOUSEEVENTF_LEFTDOWN Or MOUSEEVENTF_ABSOLUTE
mouse_event lFlags, lX, lY, 0, 0

lFlags = MOUSEEVENTF_LEFTUP Or MOUSEEVENTF_ABSOLUTE
mouse_event lFlags, lX, lY, 0, 0

End Sub


Sub FocusToExcel()
SendMouseLeftClick 100, 160
End Sub

The last Sub is the one you want to run to get the focus in Excel.
Experiment to get the coordinates right.
Put the Private declarations at the top of the module, just below Option
Explicit.


RBS

 

[Tom Ogilvy]

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

 

[RB Smissaert]

I found that that doesn't always work.
For example:

With MainForm 'the modeless userform
.Height = 32
.Width = 40
.Left = 30
.Top = 30
End With

AppActivate Application.Caption

It looks as if Excel has the focus, but for example pressing the arrow keys
doesn't change the selected cell.


RBS

 

[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.

 

[Yoam69]

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

 

[Yoam69]

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