Tuesday, May 06, 2008

Creating a Command Button on Sheet using Excel VBA

Adding an OLE Object (Command Button) to a Worksheet using Excel VBA

Sub Create_Command_Button_2007()

'

' Creates a Command button and Positions it

' Written by Shasur for http://vbadud.blogspot.com

Dim oOLE As OLEObject

' Add a Command Button

oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=220, Top:=40, Height:=30, Width:=120)

oOLE.Interior.Color = vbRed

' Move and Size with cells

oOLE.Placement = XlPlacement.xlMoveAndSize

oOLE.Object.Caption = "Click Me..."

End Sub

Each OLEObject object represents an ActiveX control or a linked or embedded OLE object.

An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other is not automatically changed to match.

13 comments:

  1. Anonymous1:22 PM

    i created a command button in excel using vba, however it creates the button everytime i click on the command object. how do i make it so that it only creates button once?

    ReplyDelete
  2. Anonymous7:12 AM

    make oOLE static, and check it every time sub is run.

    ReplyDelete
  3. Anonymous7:24 AM

    How can I assign a macro to the button?

    ReplyDelete
  4. Anonymous1:04 PM

    Right click it and choose "Assign Macro"

    ReplyDelete
  5. Anonymous1:33 AM

    Not from interface but programmatically!!

    ReplyDelete
  6. yes i want the button placed on sheet and macro assigned
    all thsi shud be done in vb itself also add the button to multiple pages
    is there a sample code u can illustrate

    ReplyDelete
  7. Anonymous9:03 AM

    .. hey.. can some1 help me add events to my commandbuttons that were added at runtime?

    ReplyDelete
  8. Anonymous1:51 AM

    This works for me.

    ActiveSheet.Buttons(1).Text = "AÑADIR REGISTROS A" & Chr(10) & "CHECKLIST"
    ActiveSheet.Buttons(1).Name = btnname
    ActiveSheet.Buttons(1).OnAction = "import_chgs"

    ReplyDelete
  9. Anonymous7:53 AM

    How can we specifically tell the location of the command button button.. For example, I want command button show up in cell (3,4)

    ReplyDelete
    Replies
    1. oOle.Top=ActiveSheet.Cells(3,4).Top
      oOle.Left=ActiveSheet.Cells(3,4).Left
      oOle.Height=ActiveSheet.Cells(3,4).Height
      oOle.Width=ActiveSheet.Cells(3,4).Width

      Delete
  10. Anonymous12:24 AM

    i copy this codes :
    Sub Create_Command_Button_2007()

    '

    ' Creates a Command button and Positions it

    ' Written by Shasur for http://vbadud.blogspot.com

    Dim oOLE As OLEObject

    ' Add a Command Button

    oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=220, Top:=40, Height:=30, Width:=120)

    oOLE.Interior.Color = vbRed

    ' Move and Size with cells

    oOLE.Placement = XlPlacement.xlMoveAndSize

    oOLE.Object.Caption = "Click Me..."

    End Sub


    ...
    and i paste it on module and i use call function .
    its create the button but the problem is it has error . " Object variable or With block variable not set " . what is it mean ?

    ReplyDelete
  11. hi i am shuja and i want to make a button on excel for save the data , and i want that when i click on the button so the form that i filled up that save automatically in the next sheet and make the reste button as well how it wil be , my email is shujaanwar@ymail.com

    ReplyDelete
  12. Anonymous11:23 AM

    Very helpful. Would you elaborate on the shape and code name of the Object. What does the NAME property change? I create 2 buttons, but the first keeps having code name set as "CommandButton1", but the shape name is what I assigned with NAME property. The 2nd button is created the same way, but shape and code names are the same (as I assigned). Strange stuff.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.