Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleUse VBA code to make, change, and delete a button in Excel
DescriptionThis example shows how to use VBA code to make, change, and delete a button in Excel.
KeywordsVBA, button, CommandButton, Excel, Office
CategoriesOffice
 
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
 
 
Copyright © 1997-2006 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated