Data Validation Listbox and the Worksheet Change Event
 

Hello,

I'm using Excel 2003.
I have a data validation listbox which happens to be contained in a
worksheet ListObject.
I have code in the Worksheet_Change event that changes a value in a cell
whose address is offset three columns from the Target.
I get an Application-defined or object-defined error at the line of code
that is making the change to the cell.
However, if I change the Target/Intersect cell to one without the Data
Validation Listbox, the error does not occur and the cell value is changed as
desired.

Does anyone know if this is a known bug in Excel? Or does anyone have any
experience with this problem and a possible workaround? I've tried all sorts
of things but to no avail.

Thanks for the help.

Here's a code snippet.

blnActiveList = False
For i = 1 To 2
Select Case i
Case 1
sList = "lstMeasurements"
sIDRng = "rngColAttributeID"
iTargetCol = 1
Case 2
sList = "lstAmenities"
sIDRng = "rngColCLID"
iTargetCol = 1
End Select

Set oList = ActiveSheet.ListObjects(sList)
Set oListRng = oList.ListColumns(iTargetCol).Range
Set oIntersectRng = Application.Intersect(Target, oListRng)

blnActiveList = oList.Active
If blnActiveList Then Exit For
Next i

If Not oIntersectRng Is Nothing Then
If Target.Address = oIntersectRng.Address Then
Application.Calculation = xlCalculationManual
For Each c In oIntersectRng.Cells
With c
If IsEmpty(.Offset(0, 3).Value) Then
..Offset(0, 3).Value = 1 'IsNew
End If
If IsEmpty(.Offset(0, 4).Value) Then
..Offset(0, 4).Value = 0 'IsDeleted
End If
If IsEmpty(.Offset(0, 5).Value) Then
..Offset(0, 5).Value = -(Abs(Application.WorksheetFunction.Min(shtProperty
..Range(sIDRng))) + 1)
End If
End With
Next c
Call subFormatListColumns(sWS:=shtProperty.Name, sListName:=sList)
Application.Calculation = xlCalculationAutomatic
End If
End If