Adding a checkbox in a row every time a user enters data in the first column of that row

[Paul]

I am using the following code to add a checkbox in column G of a
certain row every time a user enters data in that row. Somehow the
actual checkbox appears four cells above the actual cell and I cannot
seem to figure out why.
Any help would be appreciated!

Thanks,
Paul


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
If IsEmpty(Target) Then Exit Sub
Set rng = Cells(Target.Row, "G")
' Check if there is already a checkbox
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
If obj.TopLeftCell.Address = rng.Address Then
Exit Sub
End If
End If
Next

With ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.CheckBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=rng.Left, _
Top:=rng.Top, _
Width:=rng.Width, _
Height:=rng.Height)
.Object.Caption = ""
.LinkedCell = rng.Address
.Object.Value = False
End With
Application.ScreenUpdating = True

End If
End Sub

 

[Gord Dibben]

Paul

Works for me as written.

Enter anything in A1 and get a checkbox in G1.

Tested in a whack of cells down column A.

Checkbox in G on same row.

Gord Dibben Excel MVP

 

[Dave Peterson]

See one more response at your other post.

Paul

Works for me as written.

Enter anything in A1 and get a checkbox in G1.

Tested in a whack of cells down column A.

Checkbox in G on same row.

Gord Dibben Excel MVP

 

[Dave Peterson]

That was meant for the OP.

(sigh)

See one more response at your other post.

 

[Gord Dibben]

Saw your response at other post.

The "Zoom" setting correction is interesting.

Gord

 

[Dave Peterson]

Who'da thunk it? (well, Tom did!)

Saw your response at other post.

The "Zoom" setting correction is interesting.

Gord