Programmatic addition of form elements relative to a cell
 

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

Programmatic addition of form elements relative to a cell
 

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

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Check Box.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




"Matt Jensen" wrote in message
...
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

Programmatic addition of form elements relative to a cell
 

Nice one! :-)
Thanks
matt

"Tom Ogilvy" wrote in message
...
If you record a macro while you do it manually you get

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Check Box.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




"Matt Jensen" wrote in message
...
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

Programmatic addition of form elements relative to a cell
 

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" wrote in message
...
If you record a macro while you do it manually you get

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Check Box.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




"Matt Jensen" wrote in message
...
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

Programmatic addition of form elements relative to a cell
 

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.

--
Regards,
Tom Ogilvy



"Matt Jensen" wrote in message
...
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" wrote in message
...
If you record a macro while you do it manually you get

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Check Box.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




"Matt Jensen" wrote in message
...
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

Programmatic addition of form elements relative to a cell
 

Excellent - thanks Tom
Cheers
Matt

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy



"Matt Jensen" wrote in message
...
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" wrote in message
...
If you record a macro while you do it manually you get

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Check Box.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




"Matt Jensen" wrote in message
...
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