Adding a checkbox Automatically

Question

My spread sheet is set to become a very large collection of data (easily searched through filters).

Column H and I contain check boxes.

Everytime I enter new data into a new row I have to manually add these Checkboxes. My associate isnt as computer literate as I, so I would like to take this task away from him.

Is there a formula/coding I can add into the Cells  of Columns H and I that automatically populates the cell with a check box if data is input in the same row?

Answer 1

Hi Tom:

One way is to loop over checkboxes to see if one has been inserted already:

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/simple-condition-for-an-image-in-a-cell/34ea3f6b-f5d6-4114-b819-0d574c79cd7d

I would rather use a cell to "remember" the insertion:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R As Long, shp As CheckBox

Dim r1 As Range, r2 As Range, r3 As Range

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

R = Target.Row

Set r1 = Range("H" & R)

Set r2 = Range("I" & R)

Set r3 = Range("ZZ" & R)

If r3.Value = "X" Then Exit Sub

Application.EnableEvents = False

r3.Value = "X"

ActiveSheet.CheckBoxes.Add(358.5, 93, 41.25, 33).Select

Set shp = Selection

shp.Left = r1.Left

shp.Top = r1.Top

shp.Height = r1.Height

shp.Width = r1.Width

ActiveSheet.CheckBoxes.Add(358.5, 93, 41.25, 33).Select

Set shp = Selection

shp.Left = r2.Left

shp.Top = r2.Top

shp.Height = r2.Height

shp.Width = r2.Width

Target.Offset(1, 0).Select

Application.EnableEvents = True

End Sub