Application-defined or object-defined error

[Thomas Nielsen]

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

 

[JE McGimpsey]

Without knowing what inSelection() does, it sounds like something in
that routine is the culprit.

When you step through the code, does the error occur in your change
routine or in inSelection?

 

[Thomas Nielsen]

Without knowing what inSelection() does, it sounds like something in
that routine is the culprit.

A very good point; the routine doesn't do much, however,...

Function inSelection(ByRef target As Range, ByRef Selection As Range) As
Boolean
'.---------------
'| The primary (only, actually) reason for having this function is to avoid
'| saying "IF NOT NOTHING THEN" when checking for range intersections and
'| risking serious brain injury from reverse logic - in other words: return
'| true if the range Target defines the range Selection.
'`---------------
inSelection = Not Intersect(target, Selection) Is Nothing
End Function

....other than shield the reader from a double negation . I did try to
remove it and use the Intersect() call directly but it didn't help.

- Thomas

 

[JE McGimpsey]

Hmm...

Aside from my advice to never use a keyword (e.g., Selection) as a
variable name (because it leads to confusion months down the road when
you go to edit it), I don't know how that would give an
Application-defined or Object-defined error.

How is trigger_area defined? Do you have protection set in std_number?


Does Range("std_number") get set to whatever "xxx" is if you leave out
the "trigger_area" line?

 

[Thomas Nielsen]

How is trigger_area defined? Do you have protection set in std_number?

The names "std_number" and "dd_opertype" are single cells and "trigger_area"
is the name of a range of 5 cells. This is not the problem, however, since
the error also occurs if I provide either cell in the range individually.

Does Range("std_number") get set to whatever "xxx" is if you leave out
the "trigger_area" line?

It does, yes. The idea is that if the value in "std_number" is changed it
causes "dd_opertype" to update followed by the cells in "trigger_area" which
in turn update each other. A cascade, if you will. The funny thing is, that
it appears that whenever it is "trigger_area" (or any individual cell
therein) that triggers the event handler, I seem to get the error trying to
update any random cell in the workbook (not just the same sheet).

Due to time constraints I redid the entire section of the workbook with VBA
dropdowns (MSForms.ComboBox) instead of validation lists - but the original
solution was absolutely bloody elegant (well, just more scalable, really) and
I would have loved getting it to work. Still would.

- Thomas