News:

Choose a design and let our professionals help you build a successful website   - ITAcumens

Main Menu

Use VBA code to make, change, and delete a button in Excel

Started by nandagopal, Nov 03, 2008, 05:11 PM

Previous topic - Next topic

nandagopal

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