How to distribute custom toolbar updates?

[Larry Adams]

Has anyone run across a clean and automated way to distribute an Excel
workbook application containing an updated attached toolbar -- such that
it will replace any previous toolbar of the same name in a user's Excel
workspace on their PC? Everything I've seen so far suggests that one
must first delete any same named toolbar in the local Excel workspace
BEFORE the custom Excel application is loaded-- at which point any
attached custom toolbar will again be copied to the local PC. Which is
fine, and works, but is not very elegant for distribution purposes.

Any thoughts??? Much appreciated!! Larry.

 

[Rob Bovey]

Hi Larry,

I know this really doesn't answer your question, but I've found attached
toolbars to be a real nuisance. I think it's much cleaner to build your
toolbar dynamically each time your app is opened and delete it when your app
is closed.

You should always build logic into your Auto_Open (or Workbook_Open)
procedure to delete any previously existing toolbars of the same name prior
to building the toolbar for your application, in case a previous version was
left behind by a crash or some other incomplete exit of your program, and
this will also take care of any left over attached toolbars.

So a skeleton of the logic would look something like this:

Sub Auto_Open()
Dim cbrBar As CommandBar
Dim ctlControl As CommandBarControl
On Error Resume Next
''' Remove any pre-existing version of the toolbar.
CommandBars("MyToolbar").Delete
On Error GoTo 0
''' Build the new toolbar.
Set cbrBar = CommandBars.Add("MyToolbar", False)
Set ctlControl = cbrBar.Controls.Add(msoControlButton)
'''....etc....
End Sub

Sub Auto_Close()
On Error Resume Next
''' Remove the toolbar on exit.
CommandBars("MyToolbar").Delete
On Error GoTo 0
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Larry Adams]

Rob, I agree that is a clean approach, but it leaves me with at least one
problem still. When one builds a toolbar by hand, Excel allows one to create a
customized control icon image in addition to assigning a text "name". Is there
any way to use these user designed icons images on dynamically built toolbars?
And would there be any similar distribution issues?

Or,... do I need to consider building my own icon images via some 3rd party icon
program? (And, if so, any suggestions?) (Or is there a good library of images
available from somewhere that might work?) Also,...

.... would there be any way to embed these separately developed icon images
directly into the Excel template so as not to need to distribute icon files
along with the Excel app itself? The main reason being that this app may be
accessed through an intranet web page and I think I will need the Exel app to be
fully self contained. I know one can paste images into Excel, but can these
images be named and referenced?

Many thanks! Larry.

 

[Tom Ogilvy]

Paste on a sheet,
select the image, rename it using the name box in the upper left corner.

Make the sheet hidden.

In your code, copy the image to the clipboard and use pasteface to put it on
your menu button.

 

[Larry Adams]

Just as an FYI, to expand on Tom's reply, I have found that I do not need to go to a
3rd party icon program -- as Excel's toolbar "Customize" option allows one to go to
the "Command" tab, select "Modify Selection", click on your target toolbar button,
and then select "Copy Button Image", which one can then paste directly onto a
spreadsheet tab and name. Which is a nice self-contained solution -- and also
guarantees that there will be no sizing or other compatibility issues. Thanks.