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?

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 wrote:
>
> 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

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" wrote:

> 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 wrote:
> >
> > 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
>

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


DKS wrote:
>
> 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" wrote:
>
> > 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 wrote:
> > >
> > > 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
> >

--

Dave Peterson

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

Dave Peterson wrote:
>
> 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
>
> DKS wrote:
> >
> > 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" wrote:
> >
> > > 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 wrote:
> > > >
> > > > 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
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"DKS" <(E-Mail Removed)> wrote in message
news:31F7F0F6-3165-450B-B1D5-(E-Mail Removed)...
>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" wrote:
>
>> 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 wrote:
>> >
>> > 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
>>

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" wrote:

> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "DKS" <(E-Mail Removed)> wrote in message
> news:31F7F0F6-3165-450B-B1D5-(E-Mail Removed)...
> >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" wrote:
> >
> >> 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 wrote:
> >> >
> >> > 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
> >>
>
>
>

Did you try the code I suggested?

DKS wrote:
>
> 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" wrote:
>
> > 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
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Tutorials and Custom Solutions
> > Peltier Technical Services, Inc. - http://PeltierTech.com
> > _______
> >
> >
> > "DKS" <(E-Mail Removed)> wrote in message
> > news:31F7F0F6-3165-450B-B1D5-(E-Mail Removed)...
> > >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" wrote:
> > >
> > >> 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 wrote:
> > >> >
> > >> > 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
> > >>
> >
> >
> >

--

Dave Peterson

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
_______



"DKS" <(E-Mail Removed)> wrote in message
news:0791EAC3-CC57-4551-91B0-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "DKS" <(E-Mail Removed)> wrote in message
>> news:31F7F0F6-3165-450B-B1D5-(E-Mail Removed)...
>> >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" wrote:
>> >
>> >> 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 wrote:
>> >> >
>> >> > 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
>> >>
>>
>>
>>