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

Answer 1 / 8
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
Aspirhyne
 
Thank you very much for the help, it helped me too :)
0
Answer 2 / 8
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
Answer 3 / 8
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
Answer 4 / 8
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
Answer 5 / 8
coolilted Posted messages 7 Status Member
 
VBA Excel? Where?
0
Answer 6 / 8
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
Answer 7 / 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
Answer 8 / 8
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