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


--
RASEnt
------------------------------------------------------------------------
RASEnt's Profile: http://www.officehelp.in/member.php?userid=109
View this thread: http://www.officehelp.in/showthread.php?t=667632
Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.php