Dynamically add Checkboxes to worksheet columns

[Mark D'Agosta]

All,

I have a VBA function that populates a worksheet with data. I'd like to
dynamically add a checkbox to the first column of each new row that is
added. I want the checkbox to be inserted and remain within the cell. The
number of rows to be added is not known ahead of time. What's the best way
to accomplish this?

Thanks,

Mark D'Agosta
(e-mail address removed)

 

[Dick Kusleika]

Mark

Here's a sub I use to but checkboxes next to a QueryTable. Maybe you can
adapt it to your situation.

Sub UpdateList()

Dim sh As Worksheet
Dim cell As Range
Dim Rng As Range
Dim chbx As OLEObject

Set sh = ThisWorkbook.Worksheets("sheet1")

For Each chbx In sh.OLEObjects
chbx.Delete
Next chbx

sh.Columns(1).ClearContents

sh.QueryTables(1).Refresh False

Set Rng = sh.Range("b2", sh.Range("b2").End(xlDown))

sh.Range("d2:h2").Columns.AutoFit
sh.Range("a1").Rows.AutoFit
sh.Range("j1").ColumnWidth = 2

For Each cell In Rng.Cells
cell.RowHeight = 15
With sh.OLEObjects.Add("forms.checkbox.1")
.Left = cell.Offset(0, -1).Left
.Top = cell.Offset(0, -1).Top
.Width = cell.Offset(0, -1).Width
.LinkedCell = cell.Offset(0, -1).Address
.Object.Value = False
.Object.Caption = ""
End With
Next cell

Set sh = Nothing
Set cell = Nothing
Set Rng = Nothing
Set chbx = Nothing

End Sub