How to bring ActiveWorkbook to the front of the window using Excel VBA?

Question

I find that Workbook.Activate doesn't always bring that workbook to the front of the window. I wonder what is the right way to set a workbook as the top of the window so when the macro is finished this is the workbook you are looking at.

Should I use any Windows() based code or is this related to .setfocus? I am just guessing.

Answer 1

Many people will read this question with a fundamental misunderstanding:

ActiveWorkbook IS the workbook, that is in front of all other open Excel Workbooks, unless it is currently minimized. Therefore, all you need to do to guarantee it is visible on top is to un-minimize it if it is currently minimized:

With ActiveWorkbook.Windows(1)
    If .WindowState = xlMinimized Then .WindowState = xlNormal
End With                              'Alternatively: xlMaximized

However! :

ActiveWorkbook is often erroneously used to refer to the workbook containing the running code. This is wrong!

The workbook containing the running code is actually referred to as ThisWorkbook in code!

To bring ThisWorkbook in front of all other open Excel Workbooks, you can therefore use

With ThisWorkbook.Windows(1)
    .Activate
    If .WindowState = xlMinimized Then .WindowState = xlNormal
End With                              'Alternatively: xlMaximized

Note that this can not bring Excel in front windows from other Apps like browsers or the file explorer!

Bringing the Application in Front of other, external Apps

To achieve that, you can use the AppActivate statement. This is also the answer to OP's question in the literal sense ^{(probably a misunderstanding!)}, it will bring the ActiveWorkbook in front of all other open windows and apps:

AppActivate Application.Caption

To do what most people probably want, bring ThisWorkbook in front of all other open Workbooks and guarantee that Excel is in fron of all other open Apps, we can combine the statements mentioned above:

Sub BringThisWorkbookToFront()
    With ThisWorkbook.Windows(1)
        .Activate
        If .WindowState = xlMinimized Then .WindowState = xlNormal
    End With                              'Alternatively: xlMaximized
    AppActivate Application.Caption
End Sub

This should solve this problem in all cases. What is mentioned below is an alternative method that only works on Windows.

---

Alternatively, you could use the Windows API (not recommended, just for reference):

#If VBA7 Then
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hWnd As LongPtr) As Boolean
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
#Else
    Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Boolean
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#End If

Sub SetWorkbookToForeground(Optional ByVal wb As Workbook = Nothing)
    If wb Is Nothing Then Set wb = ThisWorkbook
    SetForegroundWindow FindWindow("XLMAIN", wb.Windows(1).Caption & " - Excel")
End Sub

You can use this Sub like this:

SetWorkbookToForeground ActiveWorkbook 

or just use the API functions directly like this:

SetForegroundWindow FindWindow("XLMAIN", > ActiveWorkbook.Windows(1).Caption & " - Excel") 

Words of warning again: Referring to ActiveWorkbook is almost never needed, what you most likely mean and want is ThisWorkbook. If you want to bring the workbook containing the running code in front of other open windows like browsers or the file explorer, replace ActiveWorkbook with ThisWorkbook in the above examples!

If you have the VBA IDE open while a macro is running, it will be set as foreground window after the macro finishes and I'm not aware of a way to change that.