Fire Excel Macro Only IF Specific URL Change, Exit Sub IF Not This URL

I

indi visual

New Member
Joined
Oct 15, 2010
Messages
22
Fire Macro Only IF Specific URL Address Change Is Requested From WebBrowser Object?

I am using a WebBrowser Sub that runs a macro when the URL Address changes.

The problem is it fires the macro each time the browser is refreshed because it changes (or refreshes back to itself and runs the macro).

I would like the macro only to run ONLY when a SPECIFIED URL is requested.



The code beneath works great.
All are welcome to try this code for yourself (click on the developer tab>insert tools>more tools>microsoft web browser>draw browser>paste code beneath in vba)
Code: 
Private Sub Worksheet_Activate()
WebBrowser1.Navigate  “http://www.google.com”		‘this is home page I want the web browser to start with but NOT run the macro
End Sub
Private Sub WebBrowser1_TitleChange(ByVal sText As String)
MsgBox “Macro Runs Once Web Page Change Occurs”		‘this code works, but the problem is I need the macro to only launch with a specific URL only
End Sub



The only problem is (as stated above), it launches each time the page is opened or refreshed. I am in need of a way to code this the right way so that it only runs the macro if a specified URL address is requested only. This way the macro will not fire up at start up or when the page is refreshed, but yet only when the specified page is loaded with the web browser.

Code: 
Private Sub WebBrowser1_TitleChange(ByVal sText As String)
 If address.Equals("http://www.google.com") Then
MsgBox "Macro Runs Because Of Specified URL"		‘I would like the MACRO to RUN because it EQUALS the SPECIFIED URL
        If address.Equals("http://www.YAHOO.com") Then
Do Nothing and Exit Sub						‘I would like it to DO NOTHING and EXIT SUB if the page changes to YAHOO  or anything else for the matter
End Sub


Can anyone help me with the correct working syntax for this code?
 
Sort by date Sort by votes
I tried it and it seems the if is required.

The rule is, if the title change occurs I can fire a macro (which works).

What I'm trying do is only fire the macro if a certain title is changed.
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I might misunderstand but, as a test, try copying and pasting all of this code into the sheet's code module (in a test workbook). It replaces the WebBrowser1_TitleChange code you provided earlier.

Code: 
[COLOR=Blue]Private[/COLOR] pblnEnableEvents [COLOR=Blue]As[/COLOR] [COLOR=Blue]Boolean[/COLOR]

[COLOR=Blue]Private[/COLOR] [COLOR=Blue]Sub[/COLOR] WebBrowser1_BeforeNavigate2([COLOR=Blue]ByVal[/COLOR] pDisp [COLOR=Blue]As[/COLOR] [COLOR=Blue]Object[/COLOR], _
                                    URL [COLOR=Blue]As[/COLOR] [COLOR=Blue]Variant[/COLOR], _
                                    Flags [COLOR=Blue]As[/COLOR] [COLOR=Blue]Variant[/COLOR], _
                                    TargetFrameName [COLOR=Blue]As[/COLOR] [COLOR=Blue]Variant[/COLOR], _
                                    PostData [COLOR=Blue]As[/COLOR] [COLOR=Blue]Variant[/COLOR], _
                                    Headers [COLOR=Blue]As[/COLOR] [COLOR=Blue]Variant[/COLOR], _
                                    Cancel [COLOR=Blue]As[/COLOR] [COLOR=Blue]Boolean[/COLOR])
    
    [COLOR=Blue]Const[/COLOR] strURL = "http://www.iandmyself.me/affina.production.organizer.files/button_archive/search_button_02/search_02.html"
    
    pblnEnableEvents = (WebBrowser1.LocationURL <> URL) [COLOR=Blue]And[/COLOR] (URL = strURL)
[COLOR=Blue]End[/COLOR] [COLOR=Blue]Sub[/COLOR]

[COLOR=Blue]Private[/COLOR] [COLOR=Blue]Sub[/COLOR] WebBrowser1_NavigateComplete2([COLOR=Blue]ByVal[/COLOR] pDisp [COLOR=Blue]As[/COLOR] [COLOR=Blue]Object[/COLOR], URL [COLOR=Blue]As[/COLOR] [COLOR=Blue]Variant[/COLOR])
    [COLOR=Blue]If[/COLOR] pblnEnableEvents [COLOR=Blue]Then[/COLOR] DoSomething
[COLOR=Blue]End[/COLOR] [COLOR=Blue]Sub[/COLOR]

[COLOR=Blue]Private[/COLOR] [COLOR=Blue]Sub[/COLOR] DoSomething()
    [COLOR=Blue]MsgBox[/COLOR] "Hello"
[COLOR=Blue]End[/COLOR] [COLOR=Blue]Sub[/COLOR]

The messagebox should only appear if you have navigated to the ...search_02.html URL from a different URL.
 
Upvote 0
Trying the code you provided but

Code: 
Private Sub WebBrowser1_NavigateComplete2(ByVal pDisp As Object, URL As Variant)
    If pblnEnableEvents Then DoSomething
End Sub
Private Sub DoSomething()
    MsgBox "Hello"
End Sub


But the "Hello" doesn't seem to fire. The Do Something part doesn't seem to be executing for me.
 
Upvote 0
It fires for me. Did you copy and paste all of the code into the sheet's class module (the sheet where the WebBrowser control is embedded)?
 
Last edited:
Upvote 0
Yes you are correct it does in fact fire.

It also does not mis-fire when I switch back and forth from one page tab to another.

However, it does mis-fire when I first open the page. I believe I'm 99% there, all I have to do now is prevent it from firing automatically at start up.

Any suggestions?

Because I noticed if the page is saved and closed on "search_01.html", then it actually won't fire automatically on start up; however, if the page is saved and closed with "search_02.html" selected.. then it will switch back to '01" and then mis-fire the macro upon open.

Does that make any sense (I know I can be confusing I apologize).

I am basically almost there, and your code works tip top, I just have to prevent it from auto firing at start up now.
 
Last edited:
Upvote 0
DONE.
SOLVED.
FINISHED.

WOW Thanks a million to everyone. Now I'm about to compile a list of A-list thank yous to every single individual who helped me with this in every cross posted forum I've been on this.

Along with the complete solution of course.

The finishing touches of what I did to reset the browser back to the default page was I pasted a meta tag in the header of "02.html". This way it goes back to the default page "01.html" after 1 second (a redirect tag)

Code: 
http-equiv="refresh" content="1; url=http://www.iandmyself.me/affina.production.organizer.files/button_archive/search_button_01/search_01.html"
 
Last edited:
Upvote 0
Your final touch looks great. If you're interested you can control it from VBA too.

I'm really not familiar with this control but it seems to me that when you open the workbook it is clever enough to remember where you had browsed to last and loads that page. That means that if it was at search_02.html" when you last closed the workbook it will load that page when the workbook opens and therefore call the DoSomething sub.

I had a quick look through possible properties which could be set at design time to prevent this. Nothing really jumped out at me except for the AutoLoad property which was already set to false. In the absence of a suitable property, the most obvious solution is to tell it to navigate to a "homepage" when the workbook opens. In this example I will use the "search_01.html" page.

The code you were previously using needs to be replaced with this:

Sheet1 class code module:
Code: 
[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
 
[COLOR=blue]Private[/COLOR] pblnEnableEvents [COLOR=blue]As[/COLOR] [COLOR=blue]Boolean[/COLOR]
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] WebBrowser1_BeforeNavigate2([COLOR=blue]ByVal[/COLOR] pDisp [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR], _
                                    URL [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR], _
                                    Flags [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR], _
                                    TargetFrameName [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR], _
                                    PostData [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR], _
                                    Headers [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR], _
                                    Cancel [COLOR=blue]As[/COLOR] [COLOR=blue]Boolean[/COLOR])
 
    [COLOR=blue]Const[/COLOR] strURLACTION = "http://www.iandmyself.me/affina.production.organizer.files/button_archive/search_button_02/search_02.html"
 
    pblnEnableEvents = (WebBrowser1.LocationURL <> strURLACTION) [COLOR=blue]And[/COLOR] (URL = strURLACTION)
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] WebBrowser1_NavigateComplete2([COLOR=blue]ByVal[/COLOR] pDisp [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR], URL [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR])
    [COLOR=blue]If[/COLOR] pblnEnableEvents [COLOR=blue]Then[/COLOR] DoSomething
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] DoSomething()
    [COLOR=blue]MsgBox[/COLOR] "Hello"
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] SetBrowserHomePage()
    [COLOR=blue]Const[/COLOR] strURLHOME = "http://www.iandmyself.me/affina.production.organizer.files/button_archive/search_button_01/search_01.html"
 
    WebBrowser1.Navigate2 strURLHOME
    [COLOR=blue]Do[/COLOR] [COLOR=blue]Until[/COLOR] WebBrowser1.ReadyState = READYSTATE_COMPLETE: [COLOR=blue]DoEvents[/COLOR]: [COLOR=blue]Loop[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Then, in the ThisWorkbook code module you need to add this code:

ThisWorkbook class code module:
Code: 
[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Workbook_Open()
    Sheet1.SetBrowserHomePage
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
Upvote 0
Here is the resolution for WebBrowser buttons (IE Linking specific macros to html buttons embedded within a webbrowser on an excel sheet)

1. Create 2 html pages exactly alike with different names i.e. buttonpage01 and buttonpage02
2. Create the html button (or link) on both pages
3. Assign the button on buttonpage01 to buttonpage02
4. Then, assign the button on buttonpage02 back to buttonpage01 (so that they go back and forth)
5. Now, test your html button pages in your browser to ensure they are linking back and forth correctly
6. Next, create a webbrowser object (developer tab>insert tools>microsoft web browser object)
7. In VBA, paste the following code beneath into your sheet [Sheet1 class code module:]
Code: 
Option Explicit
 Private pblnEnableEvents As Boolean
 Private Sub WebBrowser1_BeforeNavigate2(ByVal pDisp As Object, _
                                    URL As Variant, _
                                    Flags As Variant, _
                                    TargetFrameName As Variant, _
                                    PostData As Variant, _
                                    Headers As Variant, _
                                    Cancel As Boolean)
     Const strURLACTION = "http://www.yourdomain/buttonpage02.html"
     pblnEnableEvents = (WebBrowser1.LocationURL <> strURLACTION) And (URL = strURLACTION)
End Sub
 Private Sub WebBrowser1_NavigateComplete2(ByVal pDisp As Object, URL As Variant)
    If pblnEnableEvents Then DoSomething
End Sub
 Private Sub DoSomething()
    MsgBox "Hello" ‘your macro code will go here
End Sub
 Public Sub SetBrowserHomePage()
    Const strURLHOME = "http://www.yourdomain/buttonpage01.html"
     WebBrowser1.Navigate2 strURLHOME
    Do Until WebBrowser1.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
End Sub
8. Next, paste the following code beneath in [ThisWorkbook class code module:]
Code: 
Option Explicit
 Private Sub Workbook_Open()
    Sheet1.SetBrowserHomePage
End Sub
9. Test your WebBrowser buttons in excel by saving and closing (and reopening), or toggle excel tabs to refresh
10. DONE.

How it essentially works:
It runs a macro if a specific url is requested (or changed), and then returns to the default page (which is buttonpage01.html)
Why create 2 different button pages exactly alike?—Because it has to leave buttonpage01 to trigger the macro. Otherwise the change will not occur and the macro will not fire. Can this be done with flash buttons?—Absolutely, because the code monitors the page url change, and not what is actually going on in the page at all.

Is this safe for security reasons? I personally use this for offline private use, but it has been strongly recommended NOT to consider doing this for security reasons (although I’m not exactly sure why if your button html pages are contained in your own private domain).

SPECIAL RECOGNITION:)

Chandoo|KeyMaster at Chandoo.org
Hui at Chandoo.org
DonkeyOte at ExcelForum.com
Rik_UK at SocialAnswers.Microsoft.com
FlyGuy|Lost Soul at XtremeVBTalk.com
Peter_SSs at Mr.Excel.com
John_w at Mr.Excel.com
Norie at Mr.Excel.com

and last but certaintly not least a tremendous thank you to Mr.
Colin Legg @ Mr.Excel
for helping me with the finishing touches to actually pull this off.

I appreciated everyone's help on this, and so far this only tested for one button, but the victory was well worth it.
 
Last edited:
Upvote 0
Why don't you just add script to the webpage to do this?
 
Upvote 0
Because they have to talk to the excel file to tell it what to do (as far as running the excel macros), and I wasn't sure how to do that. In any case, this might have been the easiest way as it turns out. So everything worked out.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Anigito
  • Question
Get Webpage Title Text by URL Generated in MS Excel
Replies
1
Views
429
Anigito
Anigito
A
  • Question
VBA Highlight cell if contains specific text
Replies
4
Views
155
DanteAmor
DanteAmor
H
  • Solved
Macro to Exit Sub if NO is selected
Replies
6
Views
755
howard
H
J
  • Question
VBA Macro - Adding Outlook HTML signature and Referring specific cell for email body text
Replies
1
Views
1K
Micron
M
H
  • Question
Macro: Convert pdf into Excel and extract certain data to put together with file name
Replies
1
Views
2K
HeinrichPaul
H

Forum statistics

Threads
1,215,025
Messages
6,122,734
Members
449,094
Latest member
dsharae57

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top