VBA - Any way to cycle between excel window and browser window?

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
54
Hi guys.

I need to display one of my excel file on a big screen, but also need to show some data from our internal website. (In the website format, so no. Data connection is not an option)
I tried messing with the Set objShell = CreateObject("Shell.Application"), but I could only find method for .MinimizeAll.
That did minimize all windows, but that's not what I want.

The excel workbook will have multiple windows open from the same workbook, and I need to cycle between the excel app (with all of it's windows together) and the chrome browser window.

Is this possible?

Thanks
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
Try this and see if it works for you.

Basically the code should loop through each window of the workbook and bring each to the front screen for 2 seconds then it should bring the chrome browser to the front also for 2 seconds and finally it should take you back to the initial window... Set the MaximizedState Optional argument if you want to display the windows maximized.

Code in a Standard Module and run the Start routine:
Code:
Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As LongPtr, ByVal wFlag As Long) As LongPtr
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function AttachThreadInput Lib "user32" (ByVal idAttach As Long, ByVal idAttachTo As Long, ByVal fAttach As Long) As Long
    Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As LongPtr, lpdwProcessId As Long) As Long
    Private Declare PtrSafe Function GetForegroundWindow Lib "user32" () As LongPtr
    Private Declare PtrSafe Function IsIconic Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As Long, ByVal wFlag As Long) As Long
    Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function AttachThreadInput Lib "user32" (ByVal idAttach As Long, ByVal idAttachTo As Long, ByVal fAttach As Long) As Long
    Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwProcessId As Long) As Long
    Private Declare Function GetForegroundWindow Lib "user32" () As Long
    Private Declare Function IsIconic Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Sub Start()

    Application.OnTime Now, "'DisplayWindows " & True & "'"

End Sub


Sub DisplayWindows(Optional ByVal MaximizedState As Boolean = True)

    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Dim hwnd As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Dim hwnd As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    
    Dim oActiveWindow  As Window, oWnd As Window
    
    ActiveWindow.Activate
    Set oActiveWindow = ActiveWindow

    For Each oWnd In ThisWorkbook.Windows
        oWnd.Activate
        If MaximizedState Then oWnd.WindowState = xlMaximized
        Delay 2
    Next oWnd
    
    hwnd = FindWindow("Chrome_WidgetWin_1", vbNullString)
    If hwnd Then
        Call BringWindowToFront(hwnd, MaximizedState)
        Delay 2
    End If
    
    VBA.AppActivate Application.Caption
    oActiveWindow.Activate
    
    MsgBox "Back to initial window." & vbCrLf & vbCrLf & "Done!", vbInformation
    
End Sub


Private Sub Delay(DelayTime As Single)

    Dim sngTimer As Single
    
    sngTimer = Timer
    Do
        DoEvents
    Loop Until Timer - sngTimer >= DelayTime

End Sub


Private Sub BringWindowToFront(ByVal hwnd As LongPtr, Optional ByVal MaximizedState As Boolean = True)
 
    Const SW_SHOW = 5
    Const SW_SHOWMAXIMIZED = 3
    Const SW_RESTORE = 9
    Const GW_OWNER = 4
    
    Dim lThreadID1 As Long, lThreadID2 As Long
    
    On Error Resume Next
    
    If hwnd <> GetForegroundWindow() Then
        lThreadID1 = GetWindowThreadProcessId(GetForegroundWindow, ByVal 0&)
        lThreadID2 = GetWindowThreadProcessId(hwnd, ByVal 0&)
        Call AttachThreadInput(lThreadID1, lThreadID2, True)
        Call SetForegroundWindow(hwnd)
        If IsIconic(GetNextWindow(hwnd, 4)) Then
            Call ShowWindow(GetNextWindow(hwnd, GW_OWNER), IIf(MaximizedState, SW_SHOWMAXIMIZED, SW_RESTORE))
        Else
            Call ShowWindow(GetNextWindow(hwnd, GW_OWNER), IIf(MaximizedState, SW_SHOWMAXIMIZED, SW_SHOW))
        End If
        DoEvents
        Call AttachThreadInput(lThreadID2, lThreadID1, True)
    End If
End Sub
Change the Delay Time as required.
 

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
54
Hi Jaafar! Thanks very much for the solution.
I will try to implement it today.
 

Forum statistics

Threads
1,082,316
Messages
5,364,505
Members
400,803
Latest member
Niyetkhan

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top