Remove Focus from Form

[BillCPA]

I have a form that pops up on a spreadsheet whenever certain cells are
selected. It is strickly informational, and I show it modeless. After it
displays, I can still click back on the spreadsheet and enter information,
but I would like for the focus to remain on the cell on the spreadsheet
rather than go to the form.

Is there any way to do this?

 

[Chip Pearson]

You need to send a set focus message to the worksheet window. Paste
all of the following code into the userform's code module.

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA"
( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Private Declare Function FindWindowEx Lib "user32" Alias
"FindWindowExA" ( _
ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Const WM_SETFOCUS = &H7

Private Sub UserForm_Activate()
SetSheetFocus
End Sub


Private Sub SetSheetFocus()
Dim HWND_XLDesk As Long
Dim HWND_XLApp As Long
Dim HWND_XLSheet As Long
HWND_XLApp = Application.hwnd
HWND_XLDesk = FindWindowEx(HWND_XLApp, 0&, "XLDESK", vbNullString)
HWND_XLSheet = FindWindowEx(HWND_XLDesk, 0&, "EXCEL7",
ActiveWindow.Caption)
SendMessage HWND_XLSheet, WM_SETFOCUS, 0&, 0&
End Sub




Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com