New custom FaceID for automated button

[Mike]

I am trying to make a new button with my own custom icon, but am having
trouble pasting it in: Private Sub Workbook_Open()

I want to make this into an .xla add-in, but am having trouble with my
custom icon.

Based on other posts, I have tried the following 2 ideas, but they don't
work. Any help is appreciated:

APPROACH #1 - Tries to set FACEID to icon in cell I1 (does not work)
With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
' Icon is put in cell I1
.FaceId = ThisWorkbook.Worksheets("Sheet1").Range("I1").Value
.Caption = "my functionality"
.TooltipText = "my functionality"
.OnAction = "myFunctionality"
Endwith
Endwith

APRROACH #2 - uses PASTEFACE (also does not work)
Dim i As Integer
With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
' Icon is put in cell I1
.FaceId = ThisWorkbook.Worksheets("Sheet1").Range("I1").Value
.Caption = "my functionality"
.TooltipText = "my functionality"
.OnAction = "myFunctionality"
i = .ID
Endwith

ThisWorkbook.Worksheets("Sheet1").Shapes(Range("I1").Value).CopyPicture
.Controls.Item(i).PasteFace
Endwith

Once again, thanks for any help!

 

[Bob Phillips]

You need to paste the image into a worksheet and use

cbTable.Shapes(shapename).Copy­Picture
cbCtl.PasteFace

where cbTable is the codename of the sheet containing the picture, shapename
is the name of the shape, such as 'Picture 30', and cbCtl is an object
variable for the control being added.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mike]

Thanks a lot! I realized after a few tries that the shapename has to be in
quotes. It worked out!

 

[Bob Phillips]

Yeah, sorry I should have made that clear.

--

HTH

RP
(remove nothere from the email address if mailing direct)