Commandbar frustration.

[Julian Cox]

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

 

[Bob Phillips]

Julian,

I use something along the lines of Option 2. I store the specialist pictures
as images on a sheet in the xla, and paste these to buttons.

What does your code look like?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Julian Cox]

Hi bob

I use something along the lines of Option 2. I store the specialist pictures
as images on a sheet in the xla, and paste these to buttons.

Glad I'm not barking up the wrong tree. I prefer this option because
all the attributes for all the buttons are visible on one sheet of the
..vba which makes it nice and maintainable.

What does your code look like?

This is the critical part

' Work down column A, theres a number in the cell if there
' is a button to create
For Each Cell In Worksheets("Button Images").Range("A2:A30")
If Not (StrComp(Cell, "") = 0) Then

' Column C contains the picture name
ImageName = Cell.Offset(0, 2)
' Column D contains the Caption Text
CaptionText = Cell.Offset(0, 3)
' Column E contains the macro name
ButtonMacro = Cell.Offset(0, 4)

' Set transparency colour on the picture
ActiveSheet.Shapes(ImageName).Select
Selection.ShapeRange.PictureFormat.TransparentBackground _
= msoTrue
Selection.ShapeRange.PictureFormat.TransparencyColor _
= RGB(214, 211, 206)
Selection.ShapeRange.Fill.Visible = msoFalse
' not sure what this does

Set ButtonHandle = CommandBars(DestBar).Controls.Add

Worksheets("Button Images").DrawingObjects(ImageName).Copy

With ButtonHandle
.Caption = CaptionText
.Visible = True
.State = msoButtonUp
.OnAction = ButtonMacro
.PasteFace
.Enabled = False
End With
End If
Next Cell

It works perfectly except that the buttons are all 16x16 grey squares
when disabled.

Cheers

Julian

 

[Tom Ogilvy]

Maybe Excel VBA help can shed some light on #1

When a workbook is embedded in another application and activated by the user
by double-clicking the workbook, using this property with a Workbook object
returns the set of Microsoft Excel command bars available within the other
application. At all other times, using this property with a Workbook object
returns Nothing.



Your probably beyond this article, but it might be useful:

http://msdn.microsoft.com/library/techart/ofcmdbar.htm



http://support.microsoft.com/default.aspx?scid=kb;en-us;288771&Product=ofd

How To Create a Transparent Picture For Office CommandBar Buttons





http://support.microsoft.com/default.aspx?scid=kb;en-us;260850&Product=ofd

How To Add a Transparent Icon to a Toolbar Button

--

Regards,

Tom Ogilvy

 

[Julian Cox]

Hi Tom

Maybe Excel VBA help can shed some light on #1

When a workbook is embedded in another application and activated by the user
by double-clicking the workbook, using this property with a Workbook object
returns the set of Microsoft Excel command bars available within the other
application. At all other times, using this property with a Workbook object
returns Nothing.

That goes stright over my head, even on the third reading. Wibble.
What section of the help is it in? Maybe the full context will help.
All it appears to be saying is that using application.commandbars
returns the commandbars in the container application even if that is
not excel. For example if an .xls was embedded in a word document
using application.commandbars would return the word command bars.
Which is obvious.
Looking at the VBA help ThisWorkBook is exactly what I should be using
in my situation.

Your probably beyond this article, but it might be useful:

http://msdn.microsoft.com/library/techart/ofcmdbar.htm

Some of it is certainly useful, I shall come back to the section on
user-proofing command bars.

http://support.microsoft.com/default.aspx?scid=kb;en-us;288771&Product=ofd

How To Create a Transparent Picture For Office CommandBar Buttons

I thought this one had cracked it, Mask properties sound like exactly
what I need. Then I read it again and spotted 'Office XP offers a new
Picture and Mask property....'. Nuts, I'm on xl 2000. Ho Hum.

http://support.microsoft.com/default.aspx?scid=kb;en-us;260850&Product=ofd

How To Add a Transparent Icon to a Toolbar Button

This is just pasting the face from an Icon resource file instead of
from a picture object. I was really hoping not to need to do
something like this as I want to be able to distribute my code in a
single .xla file. Can an icon resource be embedded in a sheet like a
picture can? I guess I'll have to try it to find out if the paste is
done correctly. There is some hope as the blank button used as the
background is truly blank, not background coloured. It all depends on
how intelligent DrawIconEx() is I think. Can an icon have null data,
the 17th colour, for pixels like a button face does?

Thanks for the links.

Cheers

Julian

 

[Tom Ogilvy]

As I read it, unless you are going to be working in a browser or similar,
you should always use Application.CommandBars.

re: transparant icons.

The first article on Transparent did say that about XP, but if you read
further, as I understood it, and I only scanned it, it gave you a method
that did not depend on these XP only attributes. I assume its method would
be somewhat akin to the second article. It isn't something I have had a
need to do, so I haven't played with it, but it appeared to explain the
situation and my hope was that you could figure out a solution based on the
information provided.

 

[Julian Cox]

As I read it, unless you are going to be working in a browser or similar,
you should always use Application.CommandBars.

re: transparant icons.

The first article on Transparent did say that about XP, but if you read
further, as I understood it, and I only scanned it, it gave you a method
that did not depend on these XP only attributes. I assume its method would
be somewhat akin to the second article. It isn't something I have had a
need to do, so I haven't played with it, but it appeared to explain the
situation and my hope was that you could figure out a solution based on the
information provided.

Oops, my mistake. Note to self, don't jump to conclusions.

Thanks Tom.

Regards

Julian