Add custom icon using VBA
 

Hi, I have a toolbar that is stored in the personal.xls workbook and is
created each time I start Excel (2003 for xp). There are three buttons - the
first two have regular face id's, but the third I specifically designed a
button icon for. I just have no idea how to assign that icon to that macro.
I have saved the icon as a picture on sheet1 of my personal workbook (named
"calendar"), but I get an error message saying the picture is not found. I
also tried to activate the personal workbook but I kept getting more error
messages so I guess I am doing it wrong. Any help is appreciated!

Here is a snippet of my code:

c = 1
fID = 2167
Sheet1.Shapes("Calendar").Copy
For iCtr = LBound(MacNames) To UBound(MacNames)

With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIcon
If Not c = 3 Then
.FaceId = fID
Else
.PasteFace
End If
.TooltipText = TipText(iCtr)
End With
fID = fID - 2100
c = c + 1
Next iCtr

Add custom icon using VBA
 

Here's one that I saved. Maybe you can pick the pieces out.

Did you create a worksheet with 5 pictures named pic1, pic2, pic3, pic4, pic5 on
it?

For i = LBound(mac_names) To UBound(mac_names)
Worksheets("PictSheetNameHere").Pictures("pic" & i + 1).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.PasteFace
.TooltipText = tip_text(i)
End With
Next i

SLW612 wrote:

Hi, I have a toolbar that is stored in the personal.xls workbook and is
created each time I start Excel (2003 for xp). There are three buttons - the
first two have regular face id's, but the third I specifically designed a
button icon for. I just have no idea how to assign that icon to that macro.
I have saved the icon as a picture on sheet1 of my personal workbook (named
"calendar"), but I get an error message saying the picture is not found. I
also tried to activate the personal workbook but I kept getting more error
messages so I guess I am doing it wrong. Any help is appreciated!

Here is a snippet of my code:

c = 1
fID = 2167
Sheet1.Shapes("Calendar").Copy
For iCtr = LBound(MacNames) To UBound(MacNames)

With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIcon
If Not c = 3 Then
.FaceId = fID
Else
.PasteFace
End If
.TooltipText = TipText(iCtr)
End With
fID = fID - 2100
c = c + 1
Next iCtr

--

Dave Peterson

Add custom icon using VBA
 

Well ... it didn't quite work ...

I'm now getting the error message:

Run-time error '1004':
Method 'Worksheets' of object '_Global' failed

I am using face id's for two buttons and just want the one button with a
custom designed icon. 3 buttons total.

"Dave Peterson" wrote:

Here's one that I saved. Maybe you can pick the pieces out.

Did you create a worksheet with 5 pictures named pic1, pic2, pic3, pic4, pic5 on
it?

For i = LBound(mac_names) To UBound(mac_names)
Worksheets("PictSheetNameHere").Pictures("pic" & i + 1).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.PasteFace
.TooltipText = tip_text(i)
End With
Next i

SLW612 wrote:

Hi, I have a toolbar that is stored in the personal.xls workbook and is
created each time I start Excel (2003 for xp). There are three buttons - the
first two have regular face id's, but the third I specifically designed a
button icon for. I just have no idea how to assign that icon to that macro.
I have saved the icon as a picture on sheet1 of my personal workbook (named
"calendar"), but I get an error message saying the picture is not found. I
also tried to activate the personal workbook but I kept getting more error
messages so I guess I am doing it wrong. Any help is appreciated!

Here is a snippet of my code:

c = 1
fID = 2167
Sheet1.Shapes("Calendar").Copy
For iCtr = LBound(MacNames) To UBound(MacNames)

With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIcon
If Not c = 3 Then
.FaceId = fID
Else
.PasteFace
End If
.TooltipText = TipText(iCtr)
End With
fID = fID - 2100
c = c + 1
Next iCtr

--

Dave Peterson

Add custom icon using VBA
 

When you get errors, you'll want to post the code and indicate the line that
caused the error.

But maybe this will help.

If Not c = 3 Then
.FaceId = fID
Else
worksheets("somesheet").Pictures("somepic").copy
.PasteFace
End If


SLW612 wrote:

Well ... it didn't quite work ...

I'm now getting the error message:

Run-time error '1004':
Method 'Worksheets' of object '_Global' failed

I am using face id's for two buttons and just want the one button with a
custom designed icon. 3 buttons total.

"Dave Peterson" wrote:

Here's one that I saved. Maybe you can pick the pieces out.

Did you create a worksheet with 5 pictures named pic1, pic2, pic3, pic4, pic5 on
it?

For i = LBound(mac_names) To UBound(mac_names)
Worksheets("PictSheetNameHere").Pictures("pic" & i + 1).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.PasteFace
.TooltipText = tip_text(i)
End With
Next i

SLW612 wrote:

Hi, I have a toolbar that is stored in the personal.xls workbook and is
created each time I start Excel (2003 for xp). There are three buttons - the
first two have regular face id's, but the third I specifically designed a
button icon for. I just have no idea how to assign that icon to that macro.
I have saved the icon as a picture on sheet1 of my personal workbook (named
"calendar"), but I get an error message saying the picture is not found. I
also tried to activate the personal workbook but I kept getting more error
messages so I guess I am doing it wrong. Any help is appreciated!

Here is a snippet of my code:

c = 1
fID = 2167
Sheet1.Shapes("Calendar").Copy
For iCtr = LBound(MacNames) To UBound(MacNames)

With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIcon
If Not c = 3 Then
.FaceId = fID
Else
.PasteFace
End If
.TooltipText = TipText(iCtr)
End With
fID = fID - 2100
c = c + 1
Next iCtr

--

Dave Peterson


--

Dave Peterson