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.
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" <a...@Junkit.com> wrote in message
news:uSgmds1Y...@TK2MSFTNGP02.phx.gbl...
[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" <a...@Junkit.com> wrote in message
news:uapEFi5Y...@TK2MSFTNGP06.phx.gbl...
>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
> news:uMRaH%233YKH...@TK2MSFTNGP06.phx.gbl...
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
news:u5AHTY6Y...@TK2MSFTNGP02.phx.gbl...
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.
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.
"Rui Baptista" <atrey...@graffiti.net> wrote in message
news:50230757-5d4e-48ff...@g23g2000yqh.googlegroups.com...
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.
> "Rui Baptista" <atrey...@graffiti.net> wrote in message
> news:50230757-5d4e-48ff...@g23g2000yqh.googlegroups.com...
> On 12 Nov, 18:58, "Steve Smithfield" <a...@Junkit.com> 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.
>>>>>> 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.
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
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
"Rui Baptista" <atrey...@graffiti.net> wrote in message
news:351f8ffa-979c-4599...@m26g2000yqb.googlegroups.com...
Regards,
Peter Thornton
"Peter T" <peter_t@discussions> wrote in message news:OTOakXtZ...@TK2MSFTNGP02.phx.gbl...
Is there any response from Microsoft regarding the bug ?
or on a likely fix time ?
Thanks
Richard S
"Peter T" wrote:
> .
>
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
"Richard Sebastian" <Richard Seba...@discussions.microsoft.com> wrote in
message news:DF9CF2F9-DB18-4EFC...@microsoft.com...
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