Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Procedure with a worksheet change event | Excel Worksheet Functions | |||
Change event for data validation listbox | Excel Programming | |||
Data Validation & ControlSource & Change event | Excel Programming | |||
Sheet change event and list validation question | Excel Programming | |||
how to disable listbox change event | Excel Programming |