Excel: Creating custom buttons

If you want to create a custom button in Excel (or indeed any of the Microsoft Office applications)  versions 2003 and below, you can do this manually, but you are offered a dismal range of options when you click the ‘Change Button’ option.

Using a VBA macro, you can  use any of the images available in Office if you know the ID of the image you want to use (and to find out, click here) . You can create the button manually, but to illustrate this better, the code below creates a toolbar, a button, and then gives it the face image you want.

create a VBA macro  and insert the following lines:

Private Sub CreateCustomButton
Dim newBar As CommandBar

Dim newCtrl As CommandBarControl
Set newBar = CommandBars.Add

With newBar

.Name = “My Custom Toolbar”
Set newCtrl = .Controls.Add(msoControlButton)
newCtrl.Caption = “My Custom Button”
newCtrl.FaceID = (ID Number)

End With

End Sub

Comments are closed.