Hi all,

I am trying to set up a command bar and code in an .xla for a custom
Office 2000 application. All is going well so far except for the run
time creating of my command bar. Why create it at run time? Because
my command bar is only applicable to specific spreadsheets. I do not
like to clutter my users workspace, visible or otherwise, with my
application toolbar all the time. If I rely on excel adding the
toolbar to the workspace when my .xla is opened it will be there
whenever excel is run even if the user is working on an unrelated
workbook.

So I need a way to create my commandbar at run time. I have devised
two schemes, both of which have fallen at the last hurdle.


Scheme 1: Store the command bar as a command bar in the .xla

In Workbook_Open() of my .xla I have this to declutter the
workspace when my .xla is opened.

Application.CommandBars(SourceBar).Delete

Then when the target application spreadsheet is created I want to copy
the commandbar from ThisWorkBook (my .xla) to Application. (the users
workspace) but this doesn't work:

Set SourceBarHandle = ThisWorkbook.CommandBars(SourceBar)

Which is nuts because this works when the commandbar exists in the
workspace.

Set SourceBarHandle = Application.CommandBars(SourceBar)

The ThisWorkBook. form fails with 'Object variable or With block
variable not set'. The help and the autofill in the editor both think
..CommandBars is valid with ThisWorkBook.


Scheme 2: Store the button data and images on a sheet.

In this scheme I have my command button images stored as pictures on a
sheet in my .xla. I can create the commandbar at run time and by
having the transparency set correctly on the pictures the buttons look
fine when they are copied to the buttons. However, the background
pixels exist with the background colour rather than being blank as
would be the case when the button face editor is used. This means
that when the button is disabled all buttons are 16x16 grey squares
rather than being the correct shape. The smiley face on a disabled
button is a grey circle. Copy it to a picture and back to a button
and it looks fine when enabled but when disabled it is a 16x16 grey
square.

Can anyone fix either scheme?

Thanks in advance.

Julian