programming checkbox properties

[mark]

Hey.

I have a range of cells for which I would like to put a
checkbox in the center of each cell.

This works for creating the checkbox:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1",
Left:=ActiveCell.Left + ActiveCell.Width / 2 - 7,
Top:=ActiveCell.Top + ActiveCell.Height / 2 - 7,
Width:=14, Height _
:=14).Select


What I need now is to link the checkbox to the cell that
it was just placed in.

I know it's using the .LinkedCell property, but I haven't
found what vb code will do it.

??
Thanks.

 

[Tom Ogilvy]

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1",
Left:=ActiveCell.Left + ActiveCell.Width / 2 - 7,
Top:=ActiveCell.Top + ActiveCell.Height / 2 - 7,
Width:=14, Height _
:=14).Select
Selection.LinkedCell = ActiveCell.Address(external:=True)

 

[mark]

Selection.LinkedCell = ActiveCell.Address(external:=True)


Thanks, Tom.

I should have thought of that.

I think I did try Selection.LinkedCell , but I made the
mistake of giving it the cell as a range, instead of the
cell address as string text.

But it wasn't giving me a type error, it was giving me a
property not supported error, and I didn't happen to think
about needing to use the address.

Thanks for you help.

Mark

 

[mark]

Selection.LinkedCell = ActiveCell.Address(external:=True)



as a followup, can you tell me why when the checkbox is
selected, adding the code line:

Selection.Caption = ""

produces Run Time Error 38 Object doesn't support this
property or method

 

[Tom Ogilvy]

Try selection.Object.Caption

activesheet.OleObjects(1).Select
? typename(selection)
OLEObject
? selection.Object.Caption
CheckBox1

 

[mark]

Try selection.Object.Caption

That works.