9

I am trying to bring and Excel window to the front of all applications running regardless.

Current code,

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

Public Sub Bring_to_front()   
    SetForegroundWindow wb.Application.hWnd    
End Sub
Sub Test()    
     Set wb = Workbooks("MyWorkBook.xlxs")
      call Bring_to_front
End Sub

At the moment nothing happens.

2 Answers2

20

You don't need an API for this, you can use something like:

Sub BringXLToFront()
    AppActivate Application.Caption
End Sub

The AppActivate() method in VBA takes a string argument, and it will activate (i.e. bring it to the front) any window that contains that exact string.

More specific to your question though - you need to understand how APIs work in VBA a bit more - if you're using a x64 system then you need to use conditional compilation and declare the API function as pointer-safe by using the PtrSafe keyword and the LongPtr data type:

#If Win64 Then
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" _
               (ByVal hWnd As LongPtr) As LongPtr
#Else
    Private Declare Function SetForegroundWindow Lib "user32" _
               (ByVal hWnd As Long) As Long
#End If
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
6

Found the answer to what I as trying to do after a bit more research.

This will bring the worksheet you specify to the front.

Public Declare Function SetForegroundWindow _
Lib "user32" (ByVal hwnd As Long) As Long

Public Sub Bring_to_front()
    Dim setFocus As Long

    ThisWorkbook.Worksheets("Sheet1").Activate
    setfocus = SetForegroundWindow(Application.hwnd)
End Sub