Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object-defined error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object-defined error
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? In article , Thomas Nielsen wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object-defined error
"JE McGimpsey" wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object-defined error
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? In article , Thomas Nielsen wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object-defined error
"JE McGimpsey" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '50290': Application-defined or object-defined erro | Excel Discussion (Misc queries) | |||
Application-Defined or object defined error 1004 When ran on exel97 but not 2003 | Excel Programming | |||
Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming |