• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Bring To Front Macro

M

MikeLanglois

New Member
Good afternoon,


I currently have two web browser objects on a spreadsheet. WB1 is covering WB2 as WB2 has an advert at the bottom, and the way they are now, WB1 covers WB2's ad, while its add is off screen.


One of the problems I have is that on occasion, WB2 will load on top of WB1. I want to create a macro that refreshes the web browser pages, and brings WB1 on top of WB2.


So far I have:


ActiveSheet.Shapes("ReserveMap").Select

Selection.ShapeRange.ZOrder msoBringToFront

Dashboard.Map.Navigate2 "URL Link"

Dashboard.ReserveMap.Navigate2 "URL Link"


Which refreshes the map, but does not bring WB1 on top of WB2.


Can someone suggest something that will put them in the right order, without having to go into design mode and organize them myself?


Many thanks
 
Hi SirJB7,


I thought about that, however WB2 does need to be visible. It has WB1 covering the bottom quarter of it but it still needs to be visible and accessible. So I couldn't set it to false :(


Any other options?


Thanks!
 
Hi, MikeLanglois!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Bring%20To%20Front%20Macro%20%28for%20MikeLanglois%20at%20chandoo.org%29.xlsm


It uses an API call to BringWindowToTop windows function using the HWND (window handle) property of the Internet Explorer object.


This is the code for the 4 buttons "Launch", "Update 1 over 2", "Update 2 over 1" and "Stop":

-----

[pre]
Code: 
Option Explicit

' constants
Const gksURL1 = "http://www.fbi.gov"
Const gksURL2 = "http://www.cia.gov"

' declarations
Dim IE1 As InternetExplorer, IE2 As InternetExplorer
Dim glReturn As Long, giTop As Integer, giLeft As Integer

' apis
Private Declare Function BringWindowToTop Lib "user32" (ByVal hwnd As Long) As Long

Private Sub cmdLaunch_Click()
' create
Set IE1 = New InternetExplorer
Set IE2 = New InternetExplorer
' 1st
With IE1
.Visible = True
.Navigate gksURL1
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
giTop = .Top
giLeft = .Left
End With
' 2nd
With IE2
.Visible = True
.Navigate gksURL2
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
.Top = giTop
.Left = giLeft
End With
End Sub

Private Sub cmdUpdateA_Click()
' 1st up, so 2nd & 1st
With IE2
.Refresh
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
End With
With IE1
.Refresh
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
glReturn = BringWindowToTop(.hwnd)
End With
End Sub

Private Sub cmdUpdateB_Click()
' 2nd up, so 1st & 2nd
With IE1
.Refresh
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
End With
With IE2
.Refresh
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
glReturn = BringWindowToTop(.hwnd)
End With
End Sub

Private Sub cmdStop_Click()
' destroy
IE1.Visible = False
IE2.Visible = False
Set IE1 = Nothing
Set IE2 = Nothing
End Sub
[/pre]
-----


Just advise if any issue.


Regards!
 
That example is very helpful, however the web browsers are actually embedded within the Excel Document. I am using the WebBrowser Object in Excel 2007, and those are the web browsers that I need bringing to front/sending to back.
 
Hi, MikeLanglois!

I didn't get that when I read it firstly, you're right, let me see if tomorrow I have time and could try find out a real solution; if not, see you on Monday.

Regards!
 
Hi, MikeLanglois!

Sorry but I have to admit that I completely forgot this post. Gonna resume it this afternoon.

Regards!

PS: I knew that something was pending but I couldn't remember what :(
 
Hi, MikeLanglois!


Download again the updated file from same previous link and go to 2nd worksheet.

It's easier to do this with WebBrowser object since it has a BringToFront method so you don't need any API call.


The updated code is this:

-----

[pre]
Code: 
Option Explicit

' constants
Const gksURL1 = "http://www.fbi.gov"
Const gksURL2 = "http://www.cia.gov"

' declarations
Dim giTop As Integer, giLeft As Integer

Private Sub cmdLaunch_Click()
' create
'  nothing to do
' 1st
With Hoja2.wbOne
.Visible = True
.Navigate gksURL1
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
giTop = .Top
giLeft = .Left
End With
' 2nd
With Hoja2.wbTwo
.Visible = True
.Navigate gksURL2
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
.Top = giTop
.Left = giLeft
End With
End Sub

Private Sub cmdUpdateA_Click()
' 1st up, so 2nd & 1st
With Hoja2.wbTwo
.Refresh
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
End With
With Hoja2.wbOne
.Refresh
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
.BringToFront
End With
End Sub

Private Sub cmdUpdateB_Click()
' 2nd up, so 1st & 2nd
With Hoja2.wbOne
.Refresh
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
End With
With Hoja2.wbTwo
.Refresh
Do Until Not .Busy Or .ReadyState <> READYSTATE_COMPLETE
Loop
.BringToFront
End With
End Sub

Private Sub cmdStop_Click()
' destroy
Hoja2.wbOne.Visible = False
Hoja2.wbTwo.Visible = False
End Sub
[/pre]
-----


Just advise if any issue.


Regards!


PS: BTW, you might find interesting reading this link:

http://www.vbforums.com/showthread.php?384076-Webbrowser-Control-Tip-and-Examples
 
Hi, MikeLanglois!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top