D
Don Wiss
I have now invested many hours in this problem. A support firm my company
hires has also spent some hours. I can replicate the problem usually, but
some times it works. But with the macro silently failing, with no error
message, it gives few clues.
Basically the macro is unable to write to the sheet. Nor can it clear a
field. If I put a stop just before one of these, and try the next line in
immediate mode, I get a 1004 error. One characteristic sticks out. When at
the stop Alt-F11 does not flip to the sheets. If I click on the task bar
Excel tab I can go to them, but the worksheet is dead. No response to a
mouse click. No response to any keyboard action. I can't change sheets. All
I can do is go to the task bar and click to return to the macros. When
using Alt-Tab the spreadsheet does not appear as on option.
This problem is only when under a change event. A data validation drop down
list is the triggering event. Running a macro from a button push works
fine. I have used both flags and Application.EnableEvents = False to be
sure the Worksheet_Change does not get run more than once.
I am using Excel 2002 SP3. I did try loading it with repair, but it didn't
help. I did copy all the sheet with the data validation lists to a new
sheet, moved all the ranges over, then deleted the prior sheet. No effect.
Though I did a full copy and it could have copied over corruption. And I
have not tried rebuilding the entire workbook from scratch.
The problem was only dependent on input existing in a certain column. This
column, just text input, was called in a somewhat circular fashion. I took
that part of the code out and had it look for input in the next column. Now
it only fails when input is in this second column. But this column is a
period starting date and is needed.
A little background on the code. There is a column that calculates months.
They could be in any order. Then in a section below a count is done to see
how many month numbers exist. That is then decremented down a column with a
floor of 0. Then using the SMALL function it selects the month numbers,
putting the largest first.
I tend to think this is some shortcoming in Excel. But I haven't come up
with any workaround. I like using change events to control actions. And I
think this usage of SMALL to put them in descending order (someone else
wrote) is rather clever.
Don <donwiss at panix.com>.
hires has also spent some hours. I can replicate the problem usually, but
some times it works. But with the macro silently failing, with no error
message, it gives few clues.
Basically the macro is unable to write to the sheet. Nor can it clear a
field. If I put a stop just before one of these, and try the next line in
immediate mode, I get a 1004 error. One characteristic sticks out. When at
the stop Alt-F11 does not flip to the sheets. If I click on the task bar
Excel tab I can go to them, but the worksheet is dead. No response to a
mouse click. No response to any keyboard action. I can't change sheets. All
I can do is go to the task bar and click to return to the macros. When
using Alt-Tab the spreadsheet does not appear as on option.
This problem is only when under a change event. A data validation drop down
list is the triggering event. Running a macro from a button push works
fine. I have used both flags and Application.EnableEvents = False to be
sure the Worksheet_Change does not get run more than once.
I am using Excel 2002 SP3. I did try loading it with repair, but it didn't
help. I did copy all the sheet with the data validation lists to a new
sheet, moved all the ranges over, then deleted the prior sheet. No effect.
Though I did a full copy and it could have copied over corruption. And I
have not tried rebuilding the entire workbook from scratch.
The problem was only dependent on input existing in a certain column. This
column, just text input, was called in a somewhat circular fashion. I took
that part of the code out and had it look for input in the next column. Now
it only fails when input is in this second column. But this column is a
period starting date and is needed.
A little background on the code. There is a column that calculates months.
They could be in any order. Then in a section below a count is done to see
how many month numbers exist. That is then decremented down a column with a
floor of 0. Then using the SMALL function it selects the month numbers,
putting the largest first.
I tend to think this is some shortcoming in Excel. But I haven't come up
with any workaround. I like using change events to control actions. And I
think this usage of SMALL to put them in descending order (someone else
wrote) is rather clever.
Don <donwiss at panix.com>.
