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

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.

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:SQm3d.3937$4j1.3360@trndny06...
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,

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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:SQm3d.3937$4j1.3360@trndny06...
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

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").DrawingObjec ts("Picture 10").Copy

for example.

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

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:u3n3d.3938$4j1.3471@trndny06...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:SQm3d.3937$4j1.3360@trndny06...
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,

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

Frank

"Tom Ogilvy" wrote in message
...
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").DrawingObjec ts("Picture 10").Copy

for example.

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

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:u3n3d.3938$4j1.3471@trndny06...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:SQm3d.3937$4j1.3360@trndny06...
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

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

"Frank & Pam Hayes" wrote in message
news:Dxo3d.1535$kn2.1004@trndny07...
Tom,

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

Frank

"Tom Ogilvy" wrote in message
...
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").DrawingObjec ts("Picture 10").Copy

for example.

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

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:u3n3d.3938$4j1.3471@trndny06...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:SQm3d.3937$4j1.3360@trndny06...
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

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


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

"Frank & Pam Hayes" wrote in message
news:Dxo3d.1535$kn2.1004@trndny07...

Tom,

Your suggestion worked beautifully. Thank you very much for the

direction.

Frank

"Tom Ogilvy" wrote in message
...

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

Worksheets("FJHMainXLA").DrawingObjects("Pictur e 10").Copy

would be

ThisWorkbook.Worksheets("FJHMainXLA").DrawingOb jects("Picture 10").Copy

for example.

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

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:u3n3d.3938$4j1.3471@trndny06...

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" wrote in message
.. .

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.

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:SQm3d.3937$4j1.3360@trndny06...

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

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" wrote in message
...
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

"Frank & Pam Hayes" wrote in message
news:Dxo3d.1535$kn2.1004@trndny07...
Tom,

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

Frank

"Tom Ogilvy" wrote in message
...
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").DrawingObjec ts("Picture 10").Copy

for example.

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

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:u3n3d.3938$4j1.3471@trndny06...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:SQm3d.3937$4j1.3360@trndny06...
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

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" wrote in message
...
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


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

"Frank & Pam Hayes" wrote in message
news:Dxo3d.1535$kn2.1004@trndny07...

Tom,

Your suggestion worked beautifully. Thank you very much for the

direction.

Frank

"Tom Ogilvy" wrote in message
...

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

Worksheets("FJHMainXLA").DrawingObjects("Pictur e 10").Copy

would be

ThisWorkbook.Worksheets("FJHMainXLA").DrawingOb jects("Picture 10").Copy

for example.

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

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:u3n3d.3938$4j1.3471@trndny06...

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" wrote in message
.. .

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.

--
Regards,
Tom Ogilvy

"Frank & Pam Hayes" wrote in message
news:SQm3d.3937$4j1.3360@trndny06...

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

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

Robin Hammond wrote:
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" wrote in message
...

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


Robin Hammond wrote:

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