Help with very strange cell behavior

[Terry von Gease]

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]

Terry,

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

 

[steve]

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.

 

[Terry von Gease]

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

 

[steve]

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!

 

[Sandy V]

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

 

[Terry von Gease]

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]

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

 

[Tom Ogilvy]

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.

 

[Terry von Gease]

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

 

[Tom Ogilvy]

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

but I haven't a clue

I understand. Good luck.