|
|
|
| Title | Use VBA code to make, change, and delete a button in Excel |
|---|
| Description | This example shows how to use VBA code to make, change, and delete a button in Excel. |
|---|
| Keywords | VBA, button, CommandButton, Excel, Office |
|---|
| Categories | Office |
|---|
|
|
|
To add a new button to the active worksheet, use the OLEObjects collection's Add method. Use the new object's Object property to get a reference to the button itself so you can set its properties.
|
|
' Add a button to the active worksheet.
Private Sub cmdMakeButton_Click()
' Make the button.
Dim obj As OLEObject
Set obj = ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=50, Top:=50, _
Width:=100, Height:=100)
' Get the button from the OLEObject.
Dim cmd As CommandButton
Set cmd = obj.Object
' Set the button's name and picture.
obj.Name = "cmdBook"
cmd.Picture = LoadPicture("vb_prog_ref2s.bmp")
' Select it to make it visible.
obj.Select
End Sub
|
|
|
To modify the button, find it in the OLEObjects collection and use the object's Object property to get a reference to the button. Then change the button's properties.
|
|
' Change the button's picture.
Private Sub cmdChangeButton_Click()
' Find the button.
Dim obj As OLEObject
Set obj = ActiveSheet.OLEObjects("cmdBook")
' Get the button from the OLEObject.
Dim cmd As CommandButton
Set cmd = obj.Object
' Set the new picture.
cmd.Picture = LoadPicture("vbgps.bmp")
End Sub
|
|
|
To delete the button, find it in the OLEObjects collection and invoke the object's Delete method.
|
|
' Delete the button.
Private Sub cmdDeleteButton_Click()
' Find the button.
Dim obj As OLEObject
Set obj = ActiveSheet.OLEObjects("cmdBook")
obj.Delete
End Sub
|
| |
|
| |
|
|
