ansaurus

Question

How do you add a forms controls to a cell in a Excel spreadsheet for each row.

Answer 1

A:

There are several ways to do this but the easiest, assuming Excel 2007 is:

Set cb = MyWorkSheet.CheckBoxes.Add(left, top, width, height)
cb.LinkedCell = "$A$1"
cb.Display3DShading = True

You have to experiment a little with placement as I don't think there is a direct way to align the control with a particular cell. Another way would be to use the Shapes collection of the worksheet:

Set shape = MyWorkSheet.Shapes.AddFormControl(xlCheckBox, l, t, w, h)

However, keep in mind that the above method returns a Shape object and not a CheckBox object so you can't manipulate its properties directly. There are similar methods to the above like using the OLEObjects collection but that's just adding more pain.

gvkv 2010-03-08 12:09:13

is this using interop?

James Campbell 2010-03-08 19:12:54

Nope. Straight VBA. If you copy and paste that into a macro (with suitable values in the Add method) you'll get a check box linked to cell A1.

gvkv 2010-03-08 21:24:08

Also note, while this tells me how to add it, it is never really in the cell, but gave accpeted answer as the real answer seems to be NO you cannot put a forms control in a cell.

James Campbell 2010-03-09 20:08:23

By "add a forms control in excel to a cell", I took you to mean 'aligned with' (i.e. the control will move with the cell) and not 'embedded in'. You're right--only text or a formula can be added to a cell.

gvkv 2010-03-10 11:26:37

ansaurus

tags:

views:

answers:

How do you add a forms controls to a cell in a Excel spreadsheet for each row.

related questions