How to Insert a Command Button

     

  • Click on DEVELOPER Tab
  • Click on INSERT
  • Select COMMAND BUTTON
  • Place on excel sheet

    • Change the Properties of Command Button

     

  • Below is the example
  • Drag the Command Button on worksheet
  • Update the code in VBE
  • Disable DESIGN mode(already code updated in VBE)
  • Click on command button

    •  

     

    Download The Workbook

     

    Code:

    Private Sub CommandButton1_Click()
    'indicates about the Caption
    CommandButton1.Caption = "Sriguranjani"
    'indicates about the Height of command button
    CommandButton1.Height = 75
    'indicates about the Width of command button
    CommandButton1.Width = 210
    'indicates about the Back color
    CommandButton1.BackColor = RGB(255, 255, 0)
    'indicates about the fore color
    CommandButton1.ForeColor = RGB(255, 0, 0)
    'indicates about the font family
    CommandButton1.Font = "Hobo Std"
    'indicates about the font size of
    CommandButton1.Font.Size = 35
    End Sub

    Insert a Command Button Programatically

  • Insert a command button programatically in same workbook

    •  

    Code:

    Sub Edit_properties_of_cmdbutton()
    'define OLE Object
    Dim cmdbut As OLEObject
    On Error Resume Next
    'Select command button as object
    Set cmdbut = ActiveSheet.OLEObjects.Add _
    (ClassType:="Forms.CommandButton.1", Link:=False _
    , DisplayAsIcon:=False, Left:=150, Top:=14)
    'Follow the properties
    cmdbut.Object.Caption = "Sriguranjani"
    cmdbut.Height = 55
    cmdbut.Width = 125
    cmdbut.Object.Font.Name = "Hobo Std"
    cmdbut.Object.Font.Size = 21
    cmdbut.Object.BackColor = RGB(0, 0, 225)
    'cmdbut.Object.BackColor = RGB(225, 0, 0)
    'cmdbut.Object.BackColor = RGB(0, 225, 0)
    cmdbut.Object.ForeColor = RGB(0, 225, 0)
    End Sub

    Download The Workbook

    Create command Button in New Workbook

    Code:

    Private Sub CommandButton1_Click()
    Dim wkb As Workbook
    Set wkb = Workbooks.Add
    wkb.Sheets(2).Activate
    Dim cmdbut As OLEObject
    On Error Resume Next
    Set cmdbut = ActiveSheet.OLEObjects.Add _
    (ClassType:="Forms.CommandButton.1", Link:=False _
    , DisplayAsIcon:=False, Left:=450, Top:=101)
    cmdbut.Height = 35
    cmdbut.Width = 455
    cmdbut.Object.Font.Name = "Calisto MT"
    cmdbut.Object.Font.Size = 21
    cmdbut.Object.BackColor = RGB(0, 0, 225)
    'cmdbut.Object.BackColor = RGB(225, 0, 0)
    'cmdbut.Object.BackColor = RGB(0, 225, 0)
    cmdbut.Object.ForeColor = RGB(0, 225, 0)
    ActiveSheet.CommandButton1.Caption = "I am new Command Button"
    MsgBox "new command button has been created"
    End Sub

     

     

    Download The Workbook

     

     

    Program to edit the Properties of COMMAND BUTTON

     

    • Below is the example
    • Insert Command Button
    • Disable DESIGN mode(already code updated in VBE)
    • Click on command button

      •  

       

      Code:

       

       

      Download The Workbook

       

      Write a Program to insert the COMMAND BUTTON programatically and edit the properties

       

       

      Download The Workbook

       

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli