I have a strange problem with an EXcel 2003 worksheet returning
"Application-defined or object-defined error". The setup is this: A workbook
contains a number of sheets and one sheet gets its information from the
others. A section of this sheet is cells that via dynamically assigned
validation rules form some rather effective comboboxes - thus when the sheet
is loaded, one cell is set to use xlValidateList and a list is prepared from
the other worksheets and finally the value of the cell is set to the first
item in the validation list. Then another cell is set to use validation using
a similar list. If either of these cells are changed, a range of cells are
set to use validation using lists depending on the content of the first two.
All this is well. The area is named and the event handler Worksheet_Change
updates the cells as expected. Now. The last cells mentioned are also
interdependent, in the sense that if one cell is changed the following cells
get their validation list changed to reflect that change and their initial
value is set to the first item. *This* is where the error occurs. When I set
the value (cell.Value = xxx) I get an error "Application-defined or
object-defined error".

If inSelection(Target, Me.Range("dd_opertype")) Then
Me.Range("std_number").Value =
getSheetByIndex(Me.Range("dd_opertype").Value, 2, 1)
End If
If inSelection(Target, Me.Range("std_number")) Or _
inSelection(Target, Me.Range("dd_opertype")) Or _
inSelection(Target, Me.Range("trigger_area")) Then
Me.Range("std_number").Value = xxx
End If

This code is the part of the event handler reacting when specific cells are
changed. If I change the last IF statement to only include the cells named
"std_number" and "dd_opertype" everything works. The second I include the
remaining range, "trigger_area", I get the error when I make a change to the
worksheet (any of them, actually), regardless what the change is.

..--------------.
| std_number |
|--------------|
| dd_opertype |
|--------------|
| |
|- - - - - - - |
| trigger_area |
|- - - - - - - |
| |
`--------------´

Changing the validation and the cell values is done in sequence... first
remove any existing validation, then assign a value and finally set the new
validation. But since any type of assignment inside the worksheet - even
random cells outside the used range - this is hardly the culprit.

I am not so much looking for a solution as I am for an explanation. What
could be causing this reaction?

- Thomas