Terry,

There is a great code cleaner at this site (it is not from the big "M")
http://www.appspro.com/utilities/Cleaner.asp

This has helped many of us fix our files.

Also - please remember that everyone here is here to Help!

--
sb
"Terry von Gease" wrote in message
...
"steve" wrote in message
...
Terry,

Your description suggests that the issue occurs when hitting the Return
key
or selecting another cell. An event is occuring (whether there is code
or
no code).

If there is no code behind the worksheet, there could be code behind the
workbook in the ThisWorkbook module.

Excel is a "fairly simple beast" and unless the workbook is corrupted,
the
sheets and cells only do as instructed.

I am only suggesting the possibility of an event macro somewhere taking
action.

OK, in the ThisWorkBook module there are only the Open and BeforeClose
events that scurry about doing this and that. As far as anyone can tell
there is ZERO code from anywhere other than Microsoft being invoked at
Change time.

The notion of the workbook being corrupted is probably the case. Far too
often, as in a few times a day, when I'm working on this thing I get an
annoying dialog telling me, in so many words, that the workbook is hosed
in
some fashion and do I want to try to recover it and do I want to send off
to
the great white fathers at Microsoft a message detailing the malfeasance.

Recovery is useless and I couldn't give three spoons of clotted wombat
snot
for the cognoscenti at Microsoft, if they want me to debug their code for
them they can goddamn well pay me for it.

The way out from this situation is to just bail out which shuts down
Excel.
Then you have to run Excel, open the workbook that's actually the code
module without enabling macros and then save it. Then open the workbook
with
the actual user accessible worksheets also without enabling macros and
save
that. Now you can end Excel, restart it, and open the actual workbook
enabling macros which loads the workbook with the code and all is well for
a
while until it decides to give you the finger again.

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley


--
sb
"Terry von Gease" wrote in message
...
"steve" wrote in message
...
Terry,

What event macros do you have behind the sheet? Also what event
macros
do
you have in the ThisWorkbook module? Post your code.

--
sb

Not possible. There's acres of event driven code, although there's
none,
nada, zip, zero, for this sheet. It's just a scratch area. Moreover
the
code
an engine, not simple minded in-line code, thus without being there
the
actual context it's very difficult to follow. I full well realize the
concept is probably far beyond the actual grasp of Excel but not
having
to
create a fundamental list manager had a certain charm.

The point is that as far as anyone can tell there is no way to make a
cell
discard it's contents before the change event. But this one does. It
has
absolutely nothing to do with whatever code happens to be in place.

Or do you know a way to set up a cell to discard its contents prior to
the
change event? That is the salient question here.

It turn out that further examination of the phenomenon seems to
indicate
that it was somehow related to sorting on this sheet. So in every call
to
sort everywhere in the code I put an explicit 'header:=xlNo' and it
seems
to
have straightened itself out for the time being at least.

But that's no answer. I need an actual explanation for the phenomenon
before
turning this loose on an unsuspecting planet.

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley

"Terry von Gease" wrote in message
...
There is a workbook with a worksheet named "scratch" and an
assortment
of
other sheets. The sheet "scratch" is hidden, all cells locked, and
unprotected. It is always that way. It is used for all manner of
things
when
a convenient place to put data from other sheets is needed for
whatever
reasons. Sorting, diddling, what have you.

From time to time in no predictable way, cell "A1" refuses to
accept
any
data of any kind. You can un-hide the sheet and type anything you
want
into
cell "A1". It disappears as soon as you press return or select
another
cell. Gone, vanished, non-existent, not just invisible. Cleared
out,
empty.

If you delete all the cells, all the rows, all the columns, the
behavior
persists. If you go through the entire litany of locking,
unlocking,
protecting, untprotecting, etc ad nauseam it makes no difference.

If you set a breakpoint at the Change event for this sheet, type
something
in the cell, and press return, the cell is empty at the break.
This
suggests
that Excel might have a problem.

The format, color, and all that sort of stuff is identical to the
cells
around it which do no exhibit this bizarre behavior.

If you close the workbook and reopen it the cell starts acting
normally.
Then at some indeterminate time later it starts with the
inexplicable
behavior. During this time the sheet may have been cleared and/or
all
the
cells deleted any number of times and eclectic collections of
stuff
have
been placed on it, by pasting and/or simply setting values. None
of
the
stuff put there is abnormal in any way. Just all of sudden this
cell
refuses
to contain anything.

It may well be something I'm doing but I haven't a clue just what
it
might
be.

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley