PDA

View Full Version : OLEObjects.Add and Worksheet_Change event



hlev
02-24-2010, 06:50 AM
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:
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

the sheet code:
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

Thanks for your thoughts.

SamT
02-24-2010, 08:26 AM
Intersect(Range("Note"), Target)


That doesn't look right. Try:


Intersect(Range(Note, Target)


Or:


Intersect(Range(Range("Note"), Target)


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

hlev
02-27-2010, 04:21 PM
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)