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.

a nice story..
but without the code (or the offending workbook) we can only guess.
my guess: a circular event e.g one change triggering the other..
or a cell change before recalc has completed...

(inserting a doevents may solve it, or a check that calculation is
complete...)

....the point is you need to know where :)

fire the support firm. mail me the workbook. i'll have a look


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Don Wiss wrote:

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.

[snap]
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.

Don -

One stupid thing to check. The code may have

Application.ScreenUpdating = False

near the top, and it bombed before running

Application.ScreenUpdating = True

This will make Excel and the worksheet seem unresponsive.

I have a little macro I run that resets screen updating, calculation,
display alerts, and a few other things I always forget. It's called
oops(), so it's easy to find.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Don Wiss wrote:

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.