Here’s a very common question:

If I open Excel/Word (any other application), how can I ensure it comes to the front and is visible to my users?

Depending on how you start an application, depending on the version of Office, sometimes the application will automatically be on top.  Yet, in other cases it will not and then the users don’t realize the command worked properly… then they run it again and again,… and find themselves with 10 copies…

We need a reliable solution!

Luckily there is a simple way to achieve this.

The Solution

There is a simple API that can handle this for us:

#If VBA7 Then
    Public Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
#Else
    Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
#End If

Yes, that’s it! Simply Copy/Paste the code into a standard Module’s header and then you are ready to use it.

Usage Example(s)

Below are two common scenarios and how you can integrate the necessary code into your VBA procedures.

Excel

Public Sub LaunchExcel()
    '#Const EarlyBind = True 'Use Early Binding, Req. Reference Library
    #Const EarlyBind = False    'Use Late Binding
    #If EarlyBind = True Then
        'Early Binding Declarations
        Dim oExcel            As Excel.Application
        Dim oExcelWrkBk       As Excel.WorkBook
        Dim oExcelWrSht       As Excel.WorkSheet
    #Else
        'Late Binding Declaration/Constants
        Dim oExcel            As Object
        Dim oExcelWrkBk       As Object
        Dim oExcelWrSht       As Object
    #End If
    Dim bExcelOpened          As Boolean
    Dim lRetVal               As Long

    'Start Excel
    On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel
    If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oExcel = CreateObject("Excel.Application")
        bExcelOpened = False
    Else    'Excel was already running
        bExcelOpened = True
    End If
    On Error GoTo Error_Handler

    oExcel.ScreenUpdating = True
    oExcel.Visible = True    'Keep Excel hidden until we are done with our manipulation
    Set oExcelWrkBk = oExcel.Workbooks.Add()    'Start a new workbook
    Set oExcelWrSht = oExcelWrkBk.Sheets(1)

    'Bring to the Front
    lRetVal = SetForegroundWindow(oExcel.Application.hwnd)
    If lRetVal = 0 Then
        'Did not get the focus for some reason
    End If

Error_Handler_Exit:
    On Error Resume Next
    oExcel.ScreenUpdating = True
    oExcel.Visible = True
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    oExcel.ScreenUpdating = True
    Set oExcel = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: LaunchExcel" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Word

Function LaunchWord()
    Dim oWord                 As Object
    Dim oWordDoc              As Object
    Dim bWordOpened           As Boolean
    Dim lRetVal               As Long
    '    Const wdOrientPortrait = 0
    Const wdOrientLandscape = 1

    'Start Word
    On Error Resume Next
    Set oWord = GetObject("Word.Application")    'Bind to existing instance of Word
    If Err.Number <> 0 Then    'Could not get instance of Word, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oWord = CreateObject("Word.application")
        bWordOpened = False
    Else    'Word was already running
        bWordOpened = True
    End If
    On Error GoTo Error_Handler
    oWord.Visible = True   'Keep Word hidden until we are done with our manipulation
    Set oWordDoc = oWord.Documents.Add   'Start a new document
    oWordDoc.PageSetup.Orientation = wdOrientLandscape

    'Bring to the Front
    lRetVal = SetForegroundWindow(oWordDoc.Application.ActiveWindow.hwnd)
    If lRetVal = 0 Then
        'Did not get the focus for some reason
    End If

Error_Handler_Exit:
    On Error Resume Next
    oWord.Visible = True
    Set oWordDoc = Nothing
    Set oWord = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: LaunchWord" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

So as long as you can get the applications Windows Handle (Hwnd), then you can bring it to the front using this technique.

Here are a couple examples of how you can retrieving the Hwnd of a few common applications:

Access    => Application.hWndAccessApp
Excel     => Application.hwnd
Publisher => Application.ActiveWindow.hWnd
Word      => Application.ActiveWindow.hwnd

Sadly, for applications like Outlook, PowerPoint, Visio, … Microsoft has not provided a method/property to retrieve the application’s Hwnd. In such cases, when you don’t have direct access to the application’s Hwnd, you may like to review my article about a second approach that brings a Window to the ForeFront by searching for its Title/Caption:

Resources on the Subject

SetForegroundWindow function (winuser.h) - Win32 apps

Brings the thread that created the specified window into the foreground and activates the window.