Custom toolbars uninstall/re-install

[DKS]

Hi,

Few days back I had posted a question on how can I "publish" an updated
custom toolbar to my friends and colleagues?

The reply I received was to write a VBA macro that would delete my custom
toolbars on file close. And then on file open the macro to add my new
updated custom toolbar.

The deleting of custom toolbar happens fine, but I am stuck at "adding" the
custom toolbar once it has been deleted.

A practical question that bothers me: if a toolbar is explicitly deleted
then how can I get .xls to add the toolbar the next time it is opened?

Can somebody help?

 

[Dave Peterson]

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

In xl2007, those toolbars and menu modifications will show up under the addins.

 

[DKS]

i think that I have not explained my need correctly.

My custom toolbar is for activities that do not use "macros". They are
mainly for various URLs. Thus consider it as a kind of customized
"favourites" or "bookmarks" for various URLs. Having only hyperlinks behind,
I do not need any macros. Meaning that once i publish it via one .xls book
that is opened by any of my friends or colleagues, the custom toolbar gets
hooked up on their .xls. Thereafter any .xls file that they use (create or
reuse existing) the toolbar is available.

This comes in very handy.

however, if i need to update the toolbar (add more buttons or change the
hyperlinks behind existing buttons) then I need to uninstall the previous
version and get the new version installed on every one's .xls. And thus i
cannot rely on a .delete and .create kind of macro. I would prefer some
other easier way (if one exists).

I hope i have been able to explain my dilemma.

 

[Dave Peterson]

I would still use the code from Debra's site.

I modified it to point at hyperlinks instead of using macros:

Option Explicit
Public Const ToolBarName As String = "MyToolbarName"
Sub Auto_Open()
Call CreateMenubar
End Sub
Sub Auto_Close()
Call RemoveMenubar
End Sub
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub
Sub CreateMenubar()

Dim iCtr As Long

Dim URLNames As Variant
Dim CapNamess As Variant
Dim TipText As Variant

Call RemoveMenubar

URLNames = Array("http://www.nfl.com", _
"http://www.nba.com", _
"http://www.mlb.com")

CapNamess = Array("NFL", _
"NBA", _
"MLB")

TipText = Array("Click to go to the NFL site", _
"Click to go to the NBA site", _
"Click to go to the MLB site")

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For iCtr = LBound(URLNames) To UBound(URLNames)
With .Controls.Add(Type:=msoControlButton)
.HyperlinkType = msoCommandBarButtonHyperlinkOpen
.TooltipText = URLNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 71 + iCtr
End With
Next iCtr
End With
End Sub

 

[Dave Peterson]

You could delete all the TipText stuff (not the .tooltiptext!).

 

[Jon Peltier]

The "easier" way is to make the user dig around in the toolbar customization
dialog and delete it themselves. Easier for you, but a big pain for them.
Even if they delete the file, that stupid orphan toolbar will remain. And
you will be cursed for having dumped it on them.

If all the toolbar does is provide hyperlinks to web pages, why not put them
into a workbook, one URL per cell? If you change the links in the sheet, you
just need to send a new version.

If you don't like the worksheet hyperlinks, then make a real toolbar that is
created and deleted by VBA, and therefore cleans up after itself.

- Jon

 

[DKS]

Jon, Dave

My objective was to have a generic toolbar that is available via any .xls
and the people concerned are not required to open one particular .xls where
the urls are saved.

I preferred the custom toolbar, I create them manually (not via VBA). I
attach them to a spreadsheet that I ask my colleagues to open once. Once
opened and closed, the toolbar gets hooked on to their .xls; advantage any
excel file that they open (even if they open a new workbook) they see the
toolbar.

If I put all my urls in one file, then everybody is forced to open that same
file (even if it is saved on each person's hard disk). I found the toolbar
much more "user friendly" in that sense because they can access it from any
open excel workbook.

If I attach the .delete and .create type of routine then they are forced to
use one same workbook, and the advantage of accessing these handy urls from
any excel workbook is lost. Exactly same reason for my resisting your
suggestion of putting the urls in one excel file.

Hope this helps see a bit more clearly into my needs and constraints.

 

[Dave Peterson]

Did you try the code I suggested?

Jon, Dave

My objective was to have a generic toolbar that is available via any .xls
and the people concerned are not required to open one particular .xls where
the urls are saved.

I preferred the custom toolbar, I create them manually (not via VBA). I
attach them to a spreadsheet that I ask my colleagues to open once. Once
opened and closed, the toolbar gets hooked on to their .xls; advantage any
excel file that they open (even if they open a new workbook) they see the
toolbar.

If I put all my urls in one file, then everybody is forced to open that same
file (even if it is saved on each person's hard disk). I found the toolbar
much more "user friendly" in that sense because they can access it from any
open excel workbook.

If I attach the .delete and .create type of routine then they are forced to
use one same workbook, and the advantage of accessing these handy urls from
any excel workbook is lost. Exactly same reason for my resisting your
suggestion of putting the urls in one excel file.

Hope this helps see a bit more clearly into my needs and constraints.

 

[Jon Peltier]

I guess you're going to keep having problems. You'll have to rely on your
users to put up with a toolbar that doesn't go away if they don't want it or
if it's out of date, and either kill it themselves, or if you manage to kill
it with code, hunt down and open your workbook, or place the workbook in a
special directory so Excel opens it whenever it starts.

FWIW, I only very rarely use a workbook that has attached toolbars, and only
if the workbook has particular functionality that I can't get otherwise.
Microsoft claims they introduced the Ribbon because users couldn't keep
their interfaces clean, but IMHO it's practices like custom utilities and
workbooks never deleting toolbars that cause the problem. MS could have just
changed the behavior of attached toolbars, and made them go away when the
workbook was closed (as happens in Word by the way), but instead they took
away a perfectly functional menu and toolbar system and replaced it with a
monolithic, inflexible, inefficient ribbon. End of rant.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Jon, Dave

My objective was to have a generic toolbar that is available via any .xls
and the people concerned are not required to open one particular .xls
where
the urls are saved.

I preferred the custom toolbar, I create them manually (not via VBA). I
attach them to a spreadsheet that I ask my colleagues to open once. Once
opened and closed, the toolbar gets hooked on to their .xls; advantage any
excel file that they open (even if they open a new workbook) they see the
toolbar.

If I put all my urls in one file, then everybody is forced to open that
same
file (even if it is saved on each person's hard disk). I found the
toolbar
much more "user friendly" in that sense because they can access it from
any
open excel workbook.

If I attach the .delete and .create type of routine then they are forced
to
use one same workbook, and the advantage of accessing these handy urls
from
any excel workbook is lost. Exactly same reason for my resisting your
suggestion of putting the urls in one excel file.

Hope this helps see a bit more clearly into my needs and constraints.