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
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…
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.
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.
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 “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.