View original (French)

VBA - Create Button in a Specific Cell

Solved
weNgorP -  
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 Contributor 
     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 Contributor
    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
    1. lermite222 Contributor
      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
      1. lermite222 Contributor
        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
        3. 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
          1. 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
            1. 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