FollowHyperlink and Web Toolbar

If you’ve opened a file in Excel using the FollowHyperlink method, you probably know that the Web toolbar magically appears. I’ve never used the Web toolbar and I can say without hyperbole that it’s the most useless invention in the history of mankind. Tits on a boar, as we say on the farm.

You can, whenever you use FollowHyperlink, also set the Visible property of this toolbar to False. Another method is to use a class module with an application level event. I like the WindowActivate event. If a file is opened, its Window will be activated and this will catch the toolbar being made visible.

Create a class module call CAppEvents and include this code in it:

Private WithEvents xlApp As Application

Private Sub xlApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)

    xlApp.CommandBars(“Web”).Visible = False
End Sub

Property Set AppRef(aApp As Application)

    Set xlApp = aApp
End Property

If you properly set the AppRef property, the Web toolbar will never show except when the user shows it. It’s a bit draconian because it will hide the toolbar when a window is activated, even if the user showed it on purpose. That’s never a problem for me, however. Here’s some code to test how it works (if you want to test it, put this in a standard module):

Dim mApp As CAppEvents

Sub DemoHideWebToolbar()

    Dim sPath As String
    sPath = “C:Documents and SettingsDickMy DocumentsTester”
    ‘Open a workbook using FollowHyperlink
    ThisWorkbook.FollowHyperlink sPath & “Test1.xls”
    ‘Test to see if Web is visible – It is.
    MsgBox Application.CommandBars(“Web”).Visible
    ‘Create an instance of the class and
    ‘hook the application so its events fire
    Set mApp = New CAppEvents
    Set mApp.AppRef = Application
    ‘Test it again – False, this time
    ThisWorkbook.FollowHyperlink sPath & “Test2.xls”
    MsgBox Application.CommandBars(“Web”).Visible
End Sub

Posted in Uncategorized

4 thoughts on “FollowHyperlink and Web Toolbar

  1. If you disable it

    Application.CommandBars(“Web”).Enabled = False

    from the inmediate window, it will not “reappear” later on. I did that with the ‘Web’ and the ‘Review’ toolbars. I *hate* those two…

  2. Hi Dick, I use this navigation facility quite often, especially when I have more than 2 workbooks open and there are several non-contiguous values to be copied. Let’s say 6 workbooks are open. I want to copy from workbook 1 and paste in certain places in workbook 6. I have to do them one by one. If I ‘cycle’ from one workook to another using ctrl+tab, I see 4 sheets before arriving at the sheet I want in workbook 6. It is much quicker to use the blue navigation arrows instead because they send you straight back to the sheet you last visited.

  3. Frank: Hmmm. I’m so busy trying to get rid of that thing, I’ve never even tried to find anything useful on it. It’s probably worth another look.

  4. Hi There,
    Is it possible to add an image into a chart using hyperlink.
    If I am directely loading an image from the disk, I can use “Activechart.Pictures.Insert(“C:My DataMy Pictures6133180201sm.jpg”).Select”
    But how can do the same using hyperlink of that image.
    that is;
    currentchart.Pictures.FollowHyperlink “”

    Which doesn’t work!

    When I use the following, it works, but it only opens the image, but it doesn’t add that image into my chart.

    With Sheet3
    sAdd = “”

    .Parent.FollowHyperlink sAdd
    End With

    How do get that image copied to chart?

    Any help shall be highly appreciable.
    Thanks in advance.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.