On 12 Nov, 18:58, "Steve Smithfield" wrote:
Look, I was reporting an error. *The spreadsheet was not an example of good
coding practice, although whethere you like it or not there is nothing wrong
with the code. I just hacked together some code so I could replicate the
error. There are many situations which now produce that error -- the code in
my actual applications looks nothing like that. *You have completely missed
the point when you say try this and try that. *I provided but ONE EXAMPLE of
the problem. * The spreadsheet simply illustrates what I have tracked down
as the common cause: the unprotect statement.

Again: there is a serious Excel bug. *I have proved that it can be produced.
Whether you like the way I have coded it is completely irrelevant. *And now
having to change all our applications which are correctly coded because of
this bug is unacceptable.

Given there is a bug (which ocurrs in many other situations as well) I
thought people in this forum and Microsoft might like to know.

Obviously it's possible to change code to work around it (I said that in my
initial post) but that is not the point. * There is a bug which has only
just appeared with the security patch. It breaks existing applications that
have been coded correctly. * It should be fixed by Microsoft. * Your random
suggestions are not helpful and basically let Microsoft off the hook.

"Peter T" <peter_t@discussions wrote in message

...



I replicated your problem which I think is self inflicted. Referring to
your attached workbook, from the button run simply these two lines -

* *[Results].ClearContents
* *[DataArea2].ClearContents

This gives leaves me with the 'blue' from sheet2 showing on sheet1, until
refreshing the screen. I don't know exactly why that occurs but I do know
a lot of unnecessary work is involved in working out what to do with those
square brackets.

I strongly suggest do not use the lazy evaluate method, instead
* *Range("Results").ClearContents
* *Range("DataArea2").ClearContents

When done search for any [] you may have missed.

FWIW, in 2007 SP1 without the update, unprotecting sheet2, sheet1
momentarily showed the contents of sheet. IOW a flicker. In SP2 with the
update I don't see that at all.

Regards,
Peter T

"Steve Smithfield" wrote in message
...
I have a very simple spreadsheet which re-produces the problem 100% of the
time. *Attached to this post. *It has one button. Press it . *A line from
sheet2 will 'bleed' into sheet one on line 37. *If you minize Excel, then
restore it the line will no longer be showing.

You need Excel 2007 (SP2) AND KB973593 must be installed.

Let me know if you get the problem.

"Peter T" <peter_t@discussions wrote in message
.. .
I just installed KB973593 (Nov 2009 not 2007), I cannot replicate the
problem you describe in 2007 SP2. I did this

Renamed a sheet to "myName"
populated the sheet with some data, actually three tables
put a Forms button on the sheet linked to a macro with

Application.Worksheets("myName").Protect

I ran the macro, I did not see any "bleeding"

I unprotected the sheet, copied the button to another sheet and tried
again
(ie to protect a non-active sheet, again all fine. Intuitively I did not
expect a problem

* *Application.Worksheets("myName")
and
* *ActiveSheet
both return an object reference to a sheet. Once the reference is
created
the syntax used to make it is irrelevant. The only thing that might be
different for some actions is whether the sheet is active or not (you
didn't
say).

Try this -
Dim ws As Worksheet

Set ws = ActiveSheet
ws.Protect

and then
Set ws = Worksheets("worksheet name")
ws.Protect

Any difference ?

If you are still getting problems post back with full details how others
may
replicate your scenario, including any particular data, formatting,
shapes
on the sheet (if relevant), sheet name, and anything else that is
specifically required to reproduce your problem, eg some other code in
your
macro. Obviously restrict to the minimum needed.

Regards,
Peter T

"Steve Smithfield" wrote in message
...
The security patch released on 11th November for Excel 2007 causes a
significant problem with Excel 2007 VBA applications.

The bug is related to protecting and unprotecting worksheets.

The statements ActiveSheet.Protect and ActiveSheet.Unprotect work fine.

However the statement *Application.Worksheets("worksheet name").Protect
no longer works correctly. *Whilst it protects the sheet (and the
unprotect statement unprotects the sheet) it causes content from sheets
which are not selected to bleed into the selected sheet. *So if you run
a
VBA macro, eg by pressing a button on a sheet, and the macro runs the
Application.Worksheets("worksheet name").Protect * *statement bits of
other non-selected sheets bleed in the currently selected sheet.

It's clearly an Excel bug as all content which has bleeded into the
sheet
will vanish if you simply minimize the workbook and immediately
maximize
it again. *Or simply select another sheet then go back to the original
sheet.

ie it's cosmetic but you end up with a hell of a mess.

I have tested this with multiple Excel applications on Windows XP (Dell
desktop) and Vista. (Acer laptop).

I removed KB973593 and the problem vanished.

I re-installed KB973593 and the problem came back.

It's obviously possible to work around the problem, but when you have a
large number of applications it's a lot of work. *Given protecting
sheets
this way is very common and perfectly good practice I hope Microsoft
can
issue a fix ASAP.

The same problem occurs with Excel 2003 and KB973475. At my current
workplace we have around one hundred excel templates that are
potentially affected, all tested workbooks show the issues described
by Steve in XP / xl2003 and Vista / xl2007.

Suggestions are welcome, but we will not change our templates; we are
not searching for a solution, we have one solution that has been
disrupted and we expect a fix. In the meanwhile we are removing
KB973593 and KB973475. All I can say is this is a very annoying
problem caused by the mentioned updates.