Mark Garratt suggests this post – thanks for the suggestion.
You can use just about any picture as the face of a commandbarbutton. The better the picture looks when it’s small, the better it will look on your commandbar, so using that hot Jennifer Garner photo may be a bit much for a tool face. Tool faces are 16 x 16 and you can see what your image will look like by converting it in your image editing software. Or, if you’re like me, you just put on a toolbar in Excel and see what it looks like.
To get that picture on a tool, you first need to put in on a worksheet that will be accessible when the code is run. I generally put in on a hidden sheet in the same workbook as the code. Use Insert > Picture to get the picture on the worksheet.
Now that you have access to the picture, you can use the PasteFace method of the CommandBarButton object. You must first copy the picture into memory. This example creates a commandbar and a single button with a custom face.
Dim cb As CommandBar
Dim cbb As CommandBarButton
Set cb = Application.CommandBars.Add(“MyBar”)
Set cbb = cb.Controls.Add(msoControlButton)
With cbb
.OnAction = “RunMySub”
.TooltipText = “Click me to erase hard drive”
.Style = msoButtonIcon
Sheet1.Pictures(“Picture 1”).Copy
.PasteFace
End With
cb.Visible = True
End Sub
Mark also has a way to get at images that are stored externally. He uses the AddPicture method to get the picture into the sheet.
Dim cb As CommandBar
Dim cbb As CommandBarButton
Dim imgTool As Shape
Const sFILE = “C:MyButton.gif”
Const sNAME = “MyToolFace”
Set cb = Application.CommandBars.Add(“MyBar”)
Set cbb = cb.Controls.Add(msoControlButton)
With Sheet1
On Error Resume Next
Set imgTool = .Shapes(sNAME)
On Error GoTo 0
If imgTool Is Nothing Then
Set imgTool = .Shapes.AddPicture(Filename:=sFILE, _
linktofile:=msoFalse, _
savewithdocument:=msoTrue, _
Left:=.Cells(1, 1).Left, _
Top:=.Cells(1, 1).Top, _
Width:=10, Height:=10)
imgTool.Name = sNAME
End If
End With
With cbb
.OnAction = “RunMySub”
.TooltipText = “Click me to erase hard drive”
.Style = msoButtonIcon
Sheet1.Pictures(sNAME).Copy
.PasteFace
End With
cb.Visible = True
End Sub
Andy Pope has a nice addin that also offers some methods to create toolbar images
http://www.andypope.info/vba/buttoneditor.htm
There is nothing more annoying than starting Excel to paste some data only to find that an add-in has overwritten the clipboard with a tiny icon graphic.
I’ve deleted excellent add-ins for this reason alone.
Starting with Excel XP, you don’t need to use PasteFace
This Microsoft Knewledgebase article describes how:
http://support.microsoft.com/kb/286460
Rob
Rob,
That article is good if you don’t mind multiple image files being installed with the addin. Do you know if pictures saved in the addin can use this method (ie stored as shapes on the spreadsheet)?
Wilson.
I know this is an old post but I can’t seem to get this to work and I have no where else to ask
For some reason, I can’t run the following line:
Sheet1.Pictures(“Picture 1?).Copy
The Error I receive is “Unable to get the pictures property of the worksheet class”
How can I fix this?
Nevermind i figured out why.
can you tell me what you did to fix the “Unable to get pictures… ” error, as I am having the same problem
thanks