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
On Tue, 16 Mar 2010 11:34:01 -0700, BillCPA <Bill @ UAMS wrote:
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?