Excel 2007 bug caused by latest security patch KB973593

[Steve Smithfield]

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.

 

[Peter T]

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

 

[Peter T]

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]

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.

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

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.

 

[Rui Baptista]

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 codein
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.  Andnow
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.

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

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.

 

[Steve Smithfield]

I can confirm that as reported by Rui Baptista, Excel 2003 is also affected
by this error if KB973475 is installed. Removing the patch also removes the
problem. The symptoms are exaclty the same as with Excel 2007 with
KB973593. The test spreadsheet I attached in an earlier post will reproduce
the error for both Excel 2007 and Excel 2003.

To reiterate: this has nothing to do with the way we have programmed our
applications. It is an Excel bug which Microsoft should acknowledge
immediately and fix ASAP.

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.

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

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.

 

[Martin Brown]

I can confirm that as reported by Rui Baptista, Excel 2003 is also affected
by this error if KB973475 is installed. Removing the patch also removes the
problem. The symptoms are exaclty the same as with Excel 2007 with
KB973593. The test spreadsheet I attached in an earlier post will reproduce
the error for both Excel 2007 and Excel 2003.

To reiterate: this has nothing to do with the way we have programmed our
applications. It is an Excel bug which Microsoft should acknowledge
immediately and fix ASAP.

Don't hold your breath... The work around suggested looks like it works.
And I expect that eventually MSKB will recommend something along similar
lines using a flint axe to get around their introduced problem.

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.

The surprising thing to me is that it is also a bug in XL2003. Almost
all cosmetic bugs and some new race conditions are exclusive to XL2007
screen updates which are already glacially slow to begin with

Regards,
Martin Brown

 

[Rui Baptista]

Don't hold your breath... The work around suggested looks like it works.
And I expect that eventually MSKB will recommend something along similar
lines using a flint axe to get around their introduced problem.








The surprising thing to me is that it is also a bug in XL2003. Almost
all cosmetic bugs and some new race conditions are exclusive to XL2007
screen updates which are already glacially slow to begin with

Regards,
Martin Brown

It seems the bug occurs after some non-active worksheets are
unprotected:
note: I'm adding the Protect method because Unprotect can do nothing
on unprotected sheets

For Each ws In Worksheets: ws.Protect: ws.Unprotect: Next » can
"bleed"

For Each ws In Worksheets: ws.Protect: ws.Activate: ws.Unprotect: Next
» OK
For Each ws In Worksheets: ws.Activate: ActiveSheet.Protect:
ActiveSheet.Unprotect: Next » OK

The bug shows after some cell is changed:
Range("A1") = "someValue"

I am not able to replicate the problem with a clean workbook; how can
I get Steve's attached workbook?

Thank you
RB

 

[Steve Smithfield]

Rui ,

Yes, that's exactly the situation that causes the problem and what I
described in my original post. It happens when you unprotect/protect non
active worksheets.

The sample program that reproduces the problem is attached to my post of
13th November 2009. It's still there if you go to the Microsoft NNTP server.
If you are reading this through google groups or similar attachements are
removed.

If you can't get that, email me on npageiam at yahoo dot com and I'll email
it to you.

Steve

Don't hold your breath... The work around suggested looks like it works.
And I expect that eventually MSKB will recommend something along similar
lines using a flint axe to get around their introduced problem.








The surprising thing to me is that it is also a bug in XL2003. Almost
all cosmetic bugs and some new race conditions are exclusive to XL2007
screen updates which are already glacially slow to begin with

Regards,
Martin Brown

It seems the bug occurs after some non-active worksheets are
unprotected:
note: I'm adding the Protect method because Unprotect can do nothing
on unprotected sheets

For Each ws In Worksheets: ws.Protect: ws.Unprotect: Next » can
"bleed"

For Each ws In Worksheets: ws.Protect: ws.Activate: ws.Unprotect: Next
» OK
For Each ws In Worksheets: ws.Activate: ActiveSheet.Protect:
ActiveSheet.Unprotect: Next » OK

The bug shows after some cell is changed:
Range("A1") = "someValue"

I am not able to replicate the problem with a clean workbook; how can
I get Steve's attached workbook?

Thank you
RB

 

[Peter T]

It has been reported as a bug to MS

Regards,
Peter Thornton

 

[Jim Cone]

Peter,
Thanks for doing that.
I've refrained from installing all of the current Office security patches.
If one apple is bad maybe the whole bunch is bad?
Regards,
Jim Cone


It has been reported as a bug to MS

Regards,
Peter Thornton

 

[Richard Sebastian]

Hi Peter,

Is there any response from Microsoft regarding the bug ?
or on a likely fix time ?

Thanks

Richard S

 

[Peter T]

Hi Richard,

Yes MS have acknowledged it, no idea if/when it will be fixed. Or maybe it
has already, I haven't checked.

Regards,
Peter T

 

[Walt]

Hi Richard,

Yes MS have acknowledged it, no idea if/when it will be fixed. Or maybe it
has already, I haven't checked.

Regards,
Peter T

message





- Show quoted text -

Hi All, As an added piece of problem definition, the following has
been observed:
1) Win XP SP3
2) Office XP Pro, Excel 2003 & Excel 2007 loaded on the machine
3) Applications in use for 1+ years are failing in all 3 versions.

Deleting KB973593 & KB973475 restores all functionality.

Walt Weber