"Bob Phillips" wrote:

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.TransparentBack ground _
= msoTrue
Selection.ShapeRange.PictureFormat.TransparencyCol or _
= 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