Regaining Focus After Making Spreadsheet Visible To User?

[(PeteCresswell)]

MS Access VBA code is creating a spreadsheet.

The last few lines look like this:
------------------------------------------
2970 mySS.Workbooks(1).SaveAs myTargetPath


' -------------------------------------------------
' Finally, select the first sheet and make the .XLS visible to the user

2990 myWS.Select
2991 DoCmd.Hourglass False
2992 StatusSet ""
2993 MsgBox "The spreadsheet is in '" & myTargetPath & "'.", vbInformation,
"Done!"
2999 mySS.Visible = True
------------------------------------------------------

Problem is that when we get to the MsgBox prompt on line 2993, the spreadsheet
seems to have focus - even though it's not visible yet. The dialog box is
visible and the window header *looks* like it's got the focus, but just pressing
Return or Enter does not trigger the (default) OK button: the user has to click
it.

This same chain of events takes place when creating other spreadsheets and
there's no problem.

What's different about this one is that as part of the spreadsheet creation
process, I insert a VBA macro that responds to the user's click on a column
header and sorts the sheet by the contents of that column.

If I do not create that module, the problem goes away.

Seems like something's running on the sheet or something - but it shouldn't be
bc nobody clicked anything.


Anybody been here?

 

[Paul Shapiro]

I've used a Win API to make the Access application get the focus.

Declare Function pjs_apiSetForegroundWindow Lib "user32" Alias _
"SetForegroundWindow" (ByVal hWnd As Long) As Long

Call pjs_apiSetForegroundWindow(hWnd:=Application.hWndAccessApp)

Paul Shapiro

 

[(PeteCresswell)]

Per Paul Shapiro:

Call pjs_apiSetForegroundWindow(hWnd:=Application.hWndAccessApp)

That did the trick.

Thanks Paul!