Storing Bitmap Images (Drawing Objects) in an Add-In

[Frank & Pam Hayes]

I have created a workbook to hold all my favorite macros. I have created a
macro that builds a custom toobar assigning a button to each of my macros.
I have created custom Faces to use on each button, and use the PasteFace
method to load the image onto the button. The images are stored on one of
the worksheets in the file as objects, e.g "Picture 2". These images were
created by customizing a toolbar button and then copying the image to the
worksheet. The custom toolbar is created on the opening of the file and is
deleted on the closing of the file.

All this works perfectly. Howerver, I would like to figure out how to save
this as an Add-In file so that I can share it with others. When I save it
as a XLA file, everything works flawlessly EXCEPT the button images are not
showing in the toolbar. If I use one of the default Face-Ids (using the
..FaceId = # instead of the PasteFace), then the button shows the image.

I am storing the Drawing Objects on a worksheet and I know that when I
create the XLA, that Worksheet goes away. So where can I store Drawing
Objects so that the XLA can find them?

I know that there are a few thousand button images to choose from, but I
just want to see if this would work.

Thanks,

Frank

 

[Tom Ogilvy]

the sheets are hidden, but they don't go away.

I don't see any reason what you are doing shouldn't work as long as you are
not trying to select things.

 

[Frank & Pam Hayes]

Tom,

Thank you for the help, I can not tell you how many of your posts I have
read and learned from.

Maybe I am not referring to the worksheet explicitly enough. Here is the
code:

Sub CreateToolbar()
Dim cmdBar As CommandBar
Dim btnForm As CommandBarButton
' Delete the object if it already exists.
On Error Resume Next
Application.CommandBars("Frank's Favorites").Delete
'Set the CommandBar object variable.
Set cmdBar = Application.CommandBars.Add
cmdBar.Name = "Frank's Favorites"

'Add Format Input Cell Button
Worksheets("FJHMainXLA").DrawingObjects("Picture 10").Copy

With cmdBar.Controls
Set btnForm = .Add(msoControlButton)
End With
'Set the new button's properties.
With btnForm
.Style = msoButtonIconAndCaption
.Caption = " "
'.FaceId = 301
.PasteFace
.OnAction = "Format_Input_Cell"
.TooltipText = "Format Input Cell"
End With

'Add Format Header Button
Worksheets("FJHMainXLA").DrawingObjects("Picture 11").Copy
With cmdBar.Controls
Set btnForm = .Add(msoControlButton)
End With
'Set the new button's properties.
With btnForm
.Style = msoButtonIconAndCaption
.Caption = " "
'.FaceId = 333
.PasteFace
.OnAction = "Format_Header"
.TooltipText = "Format Header"
End With

'Add Gridline Toggle Button
With cmdBar.Controls
Set btnForm = .Add(msoControlButton)
End With
'Set the new button's properties.
With btnForm
.Style = msoButtonIconAndCaption
.Caption = " "
.FaceId = 86
.OnAction = "Gridlines"
.TooltipText = "Toggle Gridlines"
End With

'Add Formula Toggle Button
With cmdBar.Controls
Set btnForm = .Add(msoControlButton)
End With
'Set the new button's properties.
With btnForm
.Style = msoButtonIconAndCaption
.Caption = " "
.FaceId = 85
.OnAction = "Formulas"
.TooltipText = "Toggle Formulas"
End With

'Add Clean Selection Button
With cmdBar.Controls
Set btnForm = .Add(msoControlButton)
End With
'Set the new button's properties.
With btnForm
.Style = msoButtonIconAndCaption
.Caption = " "
.FaceId = 503
.OnAction = "CleanSelection"
.TooltipText = "Clean Selection"
End With

'Add Pivot Paste Button
Worksheets("FJHMainXLA").DrawingObjects("Picture 13").Copy
With cmdBar.Controls
Set btnForm = .Add(msoControlButton)
End With
'Set the new button's properties.
With btnForm
.Style = msoButtonIconAndCaption
.Caption = " "
'.FaceId = 2562
.PasteFace
.OnAction = "PivotPaste"
.TooltipText = "Pivot Table Paste"
End With

'Add Fill Blanks Button
Worksheets("FJHMainXLA").DrawingObjects("Picture 14").Copy
With cmdBar.Controls
Set btnForm = .Add(msoControlButton)
End With
'Set the new button's properties.
With btnForm
.Style = msoButtonIconAndCaption
.Caption = " "
'.FaceId = 294
.PasteFace
.OnAction = "FillBlanks"
.TooltipText = "Fill Blanks"
End With

'Add Delete Totals Button
Worksheets("FJHMainXLA").DrawingObjects("Picture 15").Copy
With cmdBar.Controls
Set btnForm = .Add(msoControlButton)
End With
'Set the new button's properties.
With btnForm
.Style = msoButtonIconAndCaption
.Caption = " "
'.FaceId = 292
.PasteFace
.OnAction = "Del_Total_Line"
.TooltipText = "Delete Total Lines"
End With


'Add Move Table Buton
Worksheets("FJHMainXLA").DrawingObjects("Picture 16").Copy
With cmdBar.Controls
Set btnForm = .Add(msoControlButton)
End With
'Set the new button's properties.
With btnForm
.Style = msoButtonIconAndCaption
.Caption = " "
'.FaceId = 173
.PasteFace
.OnAction = "MoveTable"
.TooltipText = "Move Pivot Table"
End With


'show the command bar
cmdBar.Visible = True
End Sub

 

[Tom Ogilvy]

Since the XLA will not be the activeworkbook, you need to qualify the sheets
with ThisWorkbook

Worksheets("FJHMainXLA").DrawingObjects("Picture 10").Copy

would be

ThisWorkbook.Worksheets("FJHMainXLA").DrawingObjects("Picture 10").Copy

for example.

If you do that to all your sheet references, I would guess that it will
work.

 

[Frank & Pam Hayes]

Tom,

Your suggestion worked beautifully. Thank you very much for the direction.

Frank

 

[Robin Hammond]

Frank,

A word of warning. I use a similar method to create toolbars on the fly.
However, I have learned from experience that in Excel 97 the PasteFace
method is very unreliable, and that it is not foolproof in Excel 2000
either. It causes an error along the lines of "method pasteface of picture
object failed" in some instances. As far as I can tell it has something to
do with the use of a non-excel and non-Windows clipboard and there is no way
to clear the clipboard on an error, even with API calls. I set the whole
thing up with an application version qualifier and now only allow text
entries on the toolbar for versions below XP by setting the style to
msoButtonCaption and skipping the pasteface method. Abridged example.

If Val(Application.Version) > 9 Then

ctlInsert.Style = msoButtonIcon
ThisWorkbook.Shapes("PIC" & RowNum).Copy
ctlInsert.PasteFace

Else

ctlInsert.Style = msoButtonCaption

End If

Robin Hammond
www.enhanceddatasystems.com

 

[Sandy V]

Robin,

However, I have learned from experience that in Excel 97 the PasteFace
method is very unreliable, and that it is not foolproof in Excel 2000
either.

I've been using PasteFace to copy an image to an icon (msoControlButton)
in Excel 97 for a long time. Never had a problem, nor received feedback
from users of a problem.

A very old Visio installer uses similar.

Not questioning what you say, rather I would like to get to the bottom
of it. I want to feel relaxed about continuing to use it. Anyone else
had a problem with PasteFace in Excel 97 ?

Regards,
Sandy

 

[Frank & Pam Hayes]

Robin,

Thank you for the additional thoughts. I will keep a eye out for the issue
you describe. Given the issues involved with updating a new version of a
toolbar for a remote user, building it on the fly each time seems much
simplier.

Frank

 

[Robin Hammond]

I've seen it on various machines, built, rebuilt, reinstalled, other users,
repeatedly.

It can also happen when you are copying and pasting charts or sheets as
pictures, particularly from Excel into Word. It might run fine a thousand
times, but the next one after that gets you. Strangely enough, you can
usually step through the code after that in the vbe. Whatever clipboard is
being used (it's not the Excel one or the standard windows one because I've
checked at length), it appears not to clear properly with no apparent cause.

Robin Hammond
www.enhanceddatasystems.com

 

[Sandy V]

Robin,

Thanks for the additional comments.
Not sure if I'm pleased to know of this potential problem, or to have
carried on in blissful ignorance of something that (hopefully) will not
arise <g>.

Regards,
Sandy