Programmatic addition of form elements relative to a cell

[Matt Jensen]

Is it possible to programmatically add a form element to a worksheet where
do you can something along the lines of specifying it's location relative to
a cell?
Thanks
Matt

 

[Tom Ogilvy]

If you record a macro while you do it manually you get

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=377.25, Top:=114.75, Width:=78, Height:=
_
18.75).Select


so you could do
lTop = Range("B9").top
lLeft = Range("B9").Left
lWidth = Range("B9").Width
lHeight = Range("B9").Height
ActiveSheet.OLEObjects.Add ClassType:="Forms.CheckBox.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=lLeft, Top:=lTop, Width:=lWidth, Height:=lHeight

 

[Matt Jensen]

Nice one!
Thanks
matt

If you record a macro while you do it manually you get

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=377.25, Top:=114.75, Width:=78, Height:=
_
18.75).Select


so you could do
lTop = Range("B9").top
lLeft = Range("B9").Left
lWidth = Range("B9").Width
lHeight = Range("B9").Height
ActiveSheet.OLEObjects.Add ClassType:="Forms.CheckBox.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=lLeft, Top:=lTop, Width:=lWidth, Height:=lHeight

--
Regards,
Tom Ogilvy




relative

 

[Matt Jensen]

Tom
In reference to the code you kindly provided below, I'm wondering about the
DisplayAsIcon property as well as images in workbooks in general.
I've seen examples of putting images on forms programmatically by loading
them from a file location, however since my excel workbook/macro will not
always be in the one location this will not work.
Is there a way to embed images in a workbook and then access them and show
them where I like in my workbook?
Thanks
Matt

 

[Tom Ogilvy]

In the menu, insert=>Picture select from file and insert your picture.

Do this on a sheet you will use for storage of the images (and will probably
hide).

Then in code you can copy and paste it to where you want.

Sub copypicture()
Worksheets("Sheet13").Shapes("Picture 1").Copy
Worksheets("Sheet2").Activate
Range("B9").Activate
ActiveSheet.Paste
End Sub

Sheet13 held the image and the sheet was hidden.

 

[Matt Jensen]

Excellent - thanks Tom
Cheers
Matt

In the menu, insert=>Picture select from file and insert your picture.

Do this on a sheet you will use for storage of the images (and will probably
hide).

Then in code you can copy and paste it to where you want.

Sub copypicture()
Worksheets("Sheet13").Shapes("Picture 1").Copy
Worksheets("Sheet2").Activate
Range("B9").Activate
ActiveSheet.Paste
End Sub

Sheet13 held the image and the sheet was hidden.