OLEObjects.Add and Worksheet_Change event

[hlev]

Hi,

I'm puzzled over the following, and seek your help:
I'm trying to create a simple spreadsheet to log notes: whenever a cell in a particular range is modified, a checkbox and a timestamp is added with VBA.

I create the checkboxes with events from a class, my problem is that I can't make the WorkSheet_Change event ignore the creation of the embedded object on the worksheet and get a "Type mismatch" error (I guess because of the change event's argument should be a Range).

the class:
[vba]Option Explicit
Private WithEvents ChkBox As MSForms.CheckBox

Public Sub createCB()
Set ChkBox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Checkbox.1", Link:=False, _
DisplayAsIcon:=False, Width:=15, Height:=15)
With ChkBox
.Caption = ""
.SpecialEffect = fmButtonEffectFlat
End With
End Sub[/vba]

the sheet code:
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
'On Error GoTo Cleanup
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("Note"), Target) Is Nothing Then
Dim CB As clsCheckBox
Set CB = New clsCheckBox
Application.EnableEvents = False
CB.createCB
End If
Cleanup:
Target.Offset(0, -1) = Date
Application.EnableEvents = True
End Sub[/vba]

Thanks for your thoughts.

[SamT]

[VBA]
Intersect(Range("Note"), Target)
[/VBA]

That doesn't look right. Try:

[VBA]
Intersect(Range(Note, Target)
[/VBA]

Or:

[VBA]
Intersect(Range(Range("Note"), Target)
[/VBA]

Sorry, I haven't had enough cafien yet to be better help

[hlev]

No worries, thanks. Intersect works fine, it's the event that fires even if the actual change on the workspace is adding an OLEObject. (despite EnableEvents is set to False)