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

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
"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

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.

--
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

"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

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

Terry,

I've experienced something sort of similar to what you
describe. Namely putting the active cell into a totally
uneditable and unchangeable mode, only noticeable when
hitting Enter failed to make any changes. Usually this
could be cleared by selecting another sheet but on
occasions only by close/reopening the wb.

In my case this was triggered in a BeforeSave proc,
setting cancel to true, then activating another sheet (no
problem if selecting a cell on the original active sheet).
Apart from the beforesave I was not trapping any other
events.

In my setup this occurred every time, in others apparently
not.

The only workaround I could figure was to move
the "activate another sheet & select cell" code to a sub
in a normal module and call this from the event proc.

If you can relate this to your issue, try a similar
workaround.

Regards,
Sandy


-----Original 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


.

"steve" wrote in message
...
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.


Perhaps it's Saved Your Bacon but it doesn't work here in my village. I
downloaded the thing, installed it, and not when I attempt to start Excel I
get an error dialog complaining about VBA code not being trusted or
something. I promptly removed it.

Nice thought but I have way too much time invested in this particular
project to subject it to the ministrations of the efforts of some amateur
enthusiast. If it weren't an amateur effort it would have run out of the
box. QED

Is there some other way to tidy things up?Preferably one that actually
works? Or am I doomed to carry around some significant burden of trash, each
piece of which with the apparent potential to **** me over. Only Microsoft
would build something that creates its own midden heap. The mind reels.

--
Terry

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

I suspect many will have read your comments with
bemusement. If Rob Bovey is merely an amateur enthusiast
what does that make the rest of us.

If it makes you more comfortable get it from Microsoft

http://support.microsoft.com/default.aspx?scid=kb;en-
us;252910
(this wrapped link should be on one line)

Regards,
Sandy

"You can lead a horse to water
but you can't make it drink"
Anon


-----Original Message-----
"steve" wrote in message
...
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.


Perhaps it's Saved Your Bacon but it doesn't work here in
my village. I
downloaded the thing, installed it, and not when I
attempt to start Excel I
get an error dialog complaining about VBA code not being
trusted or
something. I promptly removed it.

Nice thought but I have way too much time invested in
this particular
project to subject it to the ministrations of the efforts
of some amateur
enthusiast. If it weren't an amateur effort it would have
run out of the
box. QED

Is there some other way to tidy things up?Preferably one
that actually
works? Or am I doomed to carry around some significant
burden of trash, each
piece of which with the apparent potential to **** me
over. Only Microsoft
would build something that creates its own midden heap.
The mind reels.

--
Terry

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


.

Actually, this link might work better:

http://support.microsoft.com/?id=252910

The prompt you received was because of your security setting. I guess Rob
could purchase a certificate and certify his code, but that seems asking a
lot for a free utility.

--
Regards,
Tom Ogilvy


"Terry von Gease" wrote in message
...
"steve" wrote in message
...
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.


Perhaps it's Saved Your Bacon but it doesn't work here in my village. I
downloaded the thing, installed it, and not when I attempt to start Excel
I
get an error dialog complaining about VBA code not being trusted or
something. I promptly removed it.

Nice thought but I have way too much time invested in this particular
project to subject it to the ministrations of the efforts of some amateur
enthusiast. If it weren't an amateur effort it would have run out of the
box. QED

Is there some other way to tidy things up?Preferably one that actually
works? Or am I doomed to carry around some significant burden of trash,
each
piece of which with the apparent potential to **** me over. Only Microsoft
would build something that creates its own midden heap. The mind reels.

--
Terry

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

"Sandy V" wrote in message
...
I suspect many will have read your comments with
bemusement. If Rob Bovey is merely an amateur enthusiast
what does that make the rest of us.

There are precious few people on the planet capable of doing commercial
grade code. There is, on the other hand, a seemingly endless supply of those
who think that they are so qualified. If this is a sample of Mssr. Bovey's
work then he sure as hell isn't one of them. That makes him an amateur. If
the rest of you live in this being's shadow, then figure it out.

Knowing a few factoids, or having a vast supply for that matter, on tap
about some application or another does not make anyone a competent
codesmith. The two have very little, if anything, to do with each other.
Knowing what to say is vastly more important than knowing how to say it. The
former is an art and you can either do it or you can't, the latter is merely
knowledge and can be gleaned from most anywhere, witness this newsgroup, and
signifies nothing.

Moreover something that accumulates it's own toxic waste is, by definition,
the effort of amateurs . Perhaps working at full capacity but amateurs
nonetheless.


If it makes you more comfortable get it from Microsoft

http://support.microsoft.com/default.aspx?scid=kb;en-
us;252910
(this wrapped link should be on one line)

And this does exactly what for me? The link previously given at least yields
an executable for a version of Excel created in this century. This one does
not.
--
Terry

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

I went back and read your original and I was touched by your admission.

but I haven't a clue

I understand. Good luck.

--
Regards,
Tom Ogilvy