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
Juan Pablo:
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…
21 October 2004, 10:47 amFrank Brutsaert:
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.
9 December 2004, 2:01 pmDick:
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.
9 December 2004, 8:36 pmkris:
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 Data\My Pictures\6133180201sm.jpg”).Select”
But how can do the same using hyperlink of that image.
that is;
currentchart.Pictures.FollowHyperlink “http://www.imagelib.com/ML/Thumbnails/81160220.jpg”
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 = “http://www.wwusa.com/ML/Thumbnails/81160220.jpg”
.Parent.FollowHyperlink sAdd
‘ActiveChart.Paste
End With
>>
How do get that image copied to chart?
Any help shall be highly appreciable.
Thanks in advance.
Kris.
15 March 2005, 5:27 pm