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

  • Thread starter Thread starter Paul
  • Start date Start date
P

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
 
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
 
See one more response at your other post.

Gord said:
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
Click to expand...
 
Back
Top