Hello... I want to have an ActiveX checkbox appear in a
cell when text is entered in an adjacent cell. It's a
check-list, and I want the checkbox to appear only when a
list item has been entered. I assume this has to be
accomplished with a macro, but I can't figure out how.
Any help is much appreciated.

Right click on the worksheet tab that should have this behavior. Select View
Code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim OLEObj As OLEObject

If Intersect(Target, Range("b3:b99")) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub

Set OLEObj = Nothing
On Error Resume Next
Set OLEObj = Me.OLEObjects("CB_" & Target.Offset(0, -1).Address(0, 0))
On Error GoTo 0

If OLEObj Is Nothing Then
'keep going
Else
Exit Sub 'already has one
End If

With Target.Offset(0, -1)
Set OLEObj = .Parent.OLEObjects.Add(ClassType:="Forms.CheckBox. 1", _
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height)

OLEObj.Name = "CB_" & .Address(0, 0)
End With

With OLEObj
.Object.Caption = ""
.Object.Value = True
End With
End Sub

I used a standard naming convention to determine if the checkbox was there or
not. (like: CB_A3). Depending on what you're doing, you may have to determine
if it already exists in a different manner:

This might be safer:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim OLEObj As OLEObject

If Intersect(Target, Range("b3:b99")) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub

With Target.Offset(0, -1)
For Each OLEObj In Me.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
If OLEObj.TopLeftCell.Address = .Address Then
Exit Sub
End If
End If
Next OLEObj

Set OLEObj = .Parent.OLEObjects.Add(ClassType:="Forms.CheckBox. 1", _
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height)

OLEObj.Name = "CB_" & .Address(0, 0)
End With

With OLEObj
.Object.Caption = ""
.Object.Value = True
End With

End Sub


Kelly wrote:

Hello... I want to have an ActiveX checkbox appear in a
cell when text is entered in an adjacent cell. It's a
check-list, and I want the checkbox to appear only when a
list item has been entered. I assume this has to be
accomplished with a macro, but I can't figure out how.
Any help is much appreciated.

--

Dave Peterson