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
