CODEWITHSUNDEEP
vbaexcel
Nicholas
Nicholas

Reputation: 2668

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

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.

Upvotes: 3

Views: 22463

Answers (5)

GWD
GWD

Reputation: 3998

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.

Upvotes: 2

h4rm
h4rm

Reputation: 31

Just call:

Windows("Book1.xlsm").Activate

Upvotes: 3

Csaba
Csaba

Reputation: 11

I was having the same issue, and this method worked me perfectly:

Workbooks.Open (ThisWorkBook.FullName)

Since this is the current workbook you are using, it won't open an another workbook but it will activate your window even if you have other apps on top.

Upvotes: 1

Pierre Chavanet
Pierre Chavanet

Reputation: 1

I have found a trick to force the workbook display on front :

Application.DisplayAlerts = False
WB2_TO_BRING_TO_FRONT.SaveAs "Error!" 
Application.DisplayAlerts = True

Upvotes: -2

MikeD
MikeD

Reputation: 8941

Workbook on its own may not be qualifying enough, depending what you actually see on your screen at runtime. If the VBA runs within the same workbook, try

ThisWorkbook.Activate.

If your code is in workbook WB2 but processing another workbook WB1, you may want to call your VBA with that workbook as parameter and make it active at the end of your code.

So the example VBA code is in WB2 ...

Sub CallStuff()
    Debug.Print "Hey, I am " & ThisWorkbook.Name
    Debug.Print "starting to work on " & Application.Workbooks("Book1").Name

    DoStuff Application.Workbooks("Book1")

End Sub

Sub DoStuff(WB As Workbook)

    WB.Worksheets("Sheet1").[A1] = "Co-cooo!"
    'do other stuff on WB
    WB.Activate
End Sub

situation before start of VBA ... WB2 active and executing code step 1

after 2 lines of code WB2 still active, preparing subroutine with parameter step 2

processed WB1, but WB2 still active step 3

now making WB1 active step 4

Upvotes: 1

Related Questions