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
Property Set AppRef(aApp As Application)
Set xlApp = aApp
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
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.
‘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”