Worksheet_Change sub causes funny results

  • Thread starter Thread starter MWE
  • Start date Start date
M

MWE

I am running Excel2000 under WinXP. I have an Excel
application with a fair amount of VBA code. I use the
worksheet sub "Worksheet_Change(ByVal Target As Range)" to
keep track of various things done for a given worksheet.
I have used this approach for several years running
Excel2000 on Win2k. I now notice something different. If
the macros are enabled and I delete a row or column in one
of the tabs with the Worksheet_Change sub and that sub
calls any other sub, Excel hangs. I can sweep huge cell
areas and things work OK, but if I select a single row or
column, Excel hangs. Resource use remains at a few
percent, other tasks run find (while Excel is hung), etc.
I can get Excel's attention by clicking on the upper right
hand corner X. If I cancel out of the resulting close
window, everything seems OK.

Any help would be appreciated.
 
Do it again and when it starts "hanging" hit Ctrl + Break and you'll be
taken to the VBA Editor with the line that was next up for execution
hilighted. This way you'll be able to see what it's doing for all that
time. If the answer is still unclear, post your new information here.
In the meantime, though since you're an old hand here you may have
thought of this already, I once used "Worksheet_Change" in such a way
that it updated the worksheet that started the process in the first
place, causing yet another call to that bit of code. The result, at
first, was a never ending loop that always resulted in it reactivating
itself, even if the values in the sheet didn't change visibly. - Pikus
 
none of the standard things that normally stop runaways,
e.g., Cntl+c, cntl+break, etc. work. I do not think that
this is a runaway. There is virtually no CPU usage and
every other application works fine. Rather Excel seems to
be waiting for something to happen. As best as I can
tell, all of the things that were supposed to be done when
Worksheet_Change was called did occur: I have a MsgBox
debug statement as the first executable statement in
Worksheet_Change and another as the last exeucable
statement before End Sub. Both display to the screen and
then Excel hangs. It seems that "control" does not pass
back to the user.
 
Back
Top