First I love OzGrid. It has been the single most important Excel/VBA resource on the web for me.
Second (and the purpose of this post) is regarding the faceid property to the icon you want in a custom toolbar via Excel VBA. Faceid is used to return or set the Id number for the face of a command bar button control. I found the following code, used it and wanted to post it here for others to find.
For my purpose, I wanted a custom toolbar to be created in the Workbook_Open event and removed in the Workbook_Close event. This ensures users will not get the "macro not found" error message as they move/copy the file to various locations. For more on creating the toolbar programatically, see this article.
Once you have that sorted, you may want to assign specific images (faceids) to each of your buttons. To see a list of faceids, you can use the code below. This creates a custom toolbar populated with the available images and places a caption on each indicating the faceid. Modify the value of IDStop to see more images.
Sub ShowFaceIDs()
Dim NewToolbar As CommandBar
Dim NewButton As CommandBarButton
Dim i As Integer, IDStart As Integer, IDStop As Integer
' Delete existing FaceIds toolbar if it exists
On Error Resume Next
Application.CommandBars("FaceIds").Delete
On Error GoTo 0
' Add an empty toolbar
Set NewToolbar = Application.CommandBars.Add _
(Name:="FaceIds", temporary:=True)
NewToolbar.Visible = True
' Change the following values to see different FaceIDs
IDStart = 1
IDStop = 250
For i = IDStart To IDStop
Set NewButton = NewToolbar.Controls.Add _
(Type:=msoControlButton, Id:=2950)
NewButton.FaceId = i
NewButton.Caption = "FaceID = " & i
Next i
NewToolbar.Width = 600
End Sub
Display More
Happy coding!