View original (French)

VBA - Create Button in a Specific Cell

Solved
weNgorP -  
 Aspirhyne -
Hello,

I would like to know how to create a button in a specific cell using VBA, such as Range("R2").

Thank you,

Configuration: Windows XP / Internet Explorer 6.0

8 answers

  1.
    lermite222 Posted messages 9042 Status Contributor 1 199
     
     With range("R2") Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _ Link:=False, DisplayAsIcon:=False, Left:=.left, Top:=.top, Width:=.ColumnWidth, Height:=.RowHeight) End with


    --
    Experience instructs more surely than advice. (André Gide)
    If you hit a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-)(Confucius)
    7
    1. Aspirhyne
       
      Thank you very much for the help, it helped me too :)
      0
  2. lermite222 Posted messages 9042 Status Contributor 1 199
     
    Hello,
    A little search on CCM would probably have informed you
    See you later
    --
    Experience teaches more surely than advice. (André Gide)
    If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
    1
  3. lermite222 Posted messages 9042 Status Contributor 1 199
     
    It's like typing the code in the sheet module at the last line.
    --
    Experience teaches more surely than advice. (André Gide)
    If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
    1
  4. lermite222 Posted messages 9042 Status Contributor 1 199
     
    Thank you very much.......Long live the forums...
    No, no and no...
    Thank you very much.......Long live the CCM forums...
    The best... :DDDDD :o)
    See you later hi.
    Experience teaches more surely than advice. (André Gide)
    If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
    1
  6. coolilted Posted messages 7 Status Member
     
    VBA Excel? Where?
    0
  7. weNgorP
     
    Thank you,

    My problem is almost solved... The code you gave me worked well. However, I can't create my button in cell "R2"
    ... I understand that I need to adjust Left-Top-Width-Height... But it's tedious...

    Here is a small code that works well for what I want... but I can't integrate it with OLEObject to create my button...

    'Create a button that fits with a cell and name it
    Range("R2").Select
    ActiveSheet.Buttons.Add ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
    ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = "Test Button!"

    Thanks in advance....
    0
  8. weNgorP
     
    Thank you,
    by the way, here is the correct code
    Range("R2").Select
    Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
    Link:=False, DisplayAsIcon:=False, Left:=ActiveCell.Left, Top:=ActiveCell.Top, Width:=ActiveCell.Width, _
    Height:=ActiveCell.Height)
    Obj.Name = "ButtonTest"

    But I would like to better understand your code

    'Adds the macro at the end of the sheet module
    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
    .insertlines .CountOfLines + 1, Code
    End With

    I don't really understand what it does.. Could you guide me.. I'm a rookie in
    VBA..

    Thanks again,
    0
  9. weNgorP
     
    Thank you so much.......Long live the forums...It's funny, I'm new to VBA & to the forums....If I had known, I could have done better than a B in my C language course...:(...If only...

    Thanks a lot...;)

    There’s no remedy for the ifs....Too bad...
    0