Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Listbox problem
Hello, I'm using Excel 2003. I have a data validation listbox which happens to be contained in worksheet ListObject. I have code in the Worksheet_Change event that changes a value in cell whose address is offset three columns from the Target. I get an Application-defined or object-defined error at the line o code that is making the change to the cell. However, if I change the Target/Intersect cell to one without the Dat Validation Listbox, the error does not occur and the cell value i changed as desired. Does anyone know if this is a known bug in Excel? Or does anyone hav any experience with this problem and a possible workaround? I've trie 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 I -- RASEn ----------------------------------------------------------------------- RASEnt's Profile: http://www.officehelp.in/member.php?userid=10 View this thread: http://www.officehelp.in/showthread.php?t=66763 Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.ph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Data Validation (Listbox) | Excel Worksheet Functions | |||
Problem with data validation | Excel Worksheet Functions | |||
Data Validation problem | Excel Discussion (Misc queries) | |||
Setting up a validation of data listbox to provide the unique items within a range | Excel Worksheet Functions | |||
Change event for data validation listbox | Excel Programming |