Create CommandButton With Code & Position It On Sheet

  • How can I create a commandbutton with code & position it.

  • Re: Can't Name A Command Button With Code


    I would suggest doing it in another way...


    It involves creating a button instead of copying pasting, which in my oppinion is rather faulty (there always is a chance of a button being created with a name that you aren't referencing)


    Code
    1. With Worksheet(1).Buttons
    2. .Add(Worksheet(1).Range("H" & position).Left, newSheet.Range("H" & position).Top, 100, Worksheet(1).Rows(2).RowHeight * 2).Select
    3. Selection.OnAction = "Your_code"
    4. Selection.Characters.Text = "button name"
    5. End With


    Try the above, and fit it to your code. First of all... This is alot quicker, and you save a bunch of code!!

  • Re: Can't Name A Command Button With Code


    Thanks for your input!


    I though of trying that but was concerned about the positioning of the buttons.


    For example, what I have is a worksheet with now 200 rows. Column "C" of each row contains both of the command buttons. The first is named cmdWord200 the second is named cmdESD200. The buttons I want to past on the next row, 201 will be cmdWord201 and cmdESD201.


    Using your code is it possible to exactly position the buttons as the paste method does?


    I was confused about that so I attempted the paste method.


    After playing with your code I also noticed that its making form buttons, I need activex OLE buttons. My next step is to add the code for the click events of both buttons programatically.

  • Re: Can't Name A Command Button With Code


    It's no problem positioning them: as i wrote

    Code
    1. .Add(Worksheet(1).Range("H" & position).Left, newSheet.Range("H" & position).Top, 100, Worksheet(1).Rows(2).RowHeight * 2).Select


    If you want the width... Then just:

    Code
    1. position = 200
    2. .Add(Worksheet(1).Range("H" & position).Left, newSheet.Range("H" & position).Top, Worksheet(1).Range("H" & position).Width/2, Worksheet(1).Rows(position).RowHeight).Select


    and so on... The above places one button in the cell H200 with half the width of that cell but the full height. Also it fills it from the left...


    Well if you add the code programatically you know which name the procedure is given, and then you can just:

    Code
    1. Selection.OnAction = "Your_Procedure"


    The on action is a reference to a procedure!


    Hope it helps...

  • Re: Can't Name A Command Button With Code


    Yes, that makes a little more sense to me. I'll give it a try. Thanks again!