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.
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.