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.

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

"Yoam69" wrote in message
...
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.

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


--
Regards,
Tom Ogilvy



"Yoam69" wrote in message
...
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.

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" wrote in message
...
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


--
Regards,
Tom Ogilvy



"Yoam69" wrote in message
...
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.

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.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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" wrote in message
...
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


--
Regards,
Tom Ogilvy



"Yoam69" wrote in message
...
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.

Thanks Tom...that did it for me.

"Tom Ogilvy" wrote:

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


--
Regards,
Tom Ogilvy



"Yoam69" wrote in message
...
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.

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

"RB Smissaert" wrote:

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

"Yoam69" wrote in message
...
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.

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

RBS

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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" wrote in message
...
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


--
Regards,
Tom Ogilvy



"Yoam69" wrote in message
...
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.