Screen Refresh Problem

[cologeek]

Hello,

I have a large workbook with about 20 worksheets that I have used for several years without problems. Recently, I have been getting strange behavior when the screen refreshes. The biggest problem is that I get images of other worksheets covering my current worksheet when I change a cell. I can make them go away by scrolling or flipping between another worksheet and back. I have tried using manual recalc and this has helped somewhat but I can still get the images sometimes. The workbook contains VBA that performs various tasks on every worksheet (unlock, update pivots...) but these have been in there forever. There is also one piece of code that performs advanced filtering. I can only think there was something changed with a security patch, but I wanted to know if anyone else has had this issue and might be able to provide some advice on how to prevent it.

Thank you in advance for your expertise!

 

[JmME102]

I have experienced exactly your problem since Friday when two recent Excel and Office patches were applied. I have not yet tested backing out the patches to see if the problem goes away. The workbook in question had no problems until Friday, and now after workbook_open runs, various buttons and cell 'ghosts' appear on the active sheet at the completion of workbook_open. Clicking on other sheets and going back to the original clears the 'ghosts', but this can happen at other times as well during the use of the workbook. I have been whittling down the code and believe it has something to do with the act of worksheet protection and unprotection during the execution of workbook_open. At this point I have removed almost all of the code except the sheet protect/unprotects with a write in between. I'd be interested in hearing more experiences... I do suspect the patches introduced a problem as well.

 

[JmME102]

I've played with this quite a bit now and have it boiled down to this. I would call it an Excel bug, and I still think it was just recentlyl introduced.

In Excel 2007, make a workbook with 2 sheets. On sheet1, add say 5 activex buttons (Developer tab, insert, activex, choose button). In VB, add the following code to Thisworkbook:

Sub workbook_open()

Application.ScreenUpdating = False
Worksheets("Sheet1").Unprotect
Worksheets("Sheet1").Protect
Application.ScreenUpdating = True

End Sub


Now click the Sheet2 tab to activate sheet2, and save the workbook.

Nowopen the workbook. You will see ghosts of the buttons from sheet1 on sheet2, but they aren't really there. If you comment out the screenupdating=true line, or if sheet1 is active to begin with, or if you have less than 4 or 5 buttons, there is no problem.

Microsoft.... are you listening?

 

[cologeek]

Thank you A TON for your responses. Your thoughts and input were extremely valuable in helping me fix this problem. Thanks again!!

 

[vbExcel]

Just in case anyone is following this thread, I'm sorry to be late, I'm recovering from prostate cancer treatment. I've had the same problem, even without unprotecting/protecting the Sheet. My app consists of two Workbooks, both buttoned down tight and never unprotected except manually by me to update.

I started by commenting out the .ScreenUpdating=False Statements and forgot the True. The problem continued. When the True Statement was turned off, the phantom buttons and page scraps went away. I wasn't satisfied though, because of the increased flickering that .ScreenUpdating was turned off for to begin with.

So my workaround is allow .ScreenUpdating=False and not setting it true in the Workbook.Open Event. Instead, my last line calls ActiveSheet.Activate, which sets it True outside the Workbook.Open Event.

The final result is that the phantom artifacts do appear momentarily and then go away without user intervention being required. Ugly at best, but I prefer it to anything else I've tried. If you like my idea, go for it!

Here's hoping the Office updates in this month's patches restore order!

Best regards

 

[GSnyder]

I've just experienced this same problem and found this thread helpful. I have a rather complex workbook with quite a bit of code that has worked well for years. Recently, however, I started having the screen repainting problem as well. In my case, the problem seems to be the pieces of code which protect and unprotect the sheets (as part of the process, we periodically allow data entry, so the code is continually protecting and unprotecting). After this code ran, I would have the repainting problem when calculation occurred:

Sub Protection()
Dim i As Integer
For i = 1 To Worksheets.Count
Worksheets(i).Protect
Next
End Sub

The problem seems to be in that I'm protecting the sheet without selecting it first. If I revise my code to the following, it seems to fix the problem:

Sub Protection()
Dim i As Integer
Dim strWorksheet As String
strWorksheet = ActiveSheet.Name
For i = 1 To Worksheets.Count
Worksheets(i).Select
Worksheets(i).Protect
Next

Worksheets(strWorksheet).Select
End Sub

So far, this seems to be working well. I don't know if it's a new patch of Excel that my I/T department sent out or what, but, for now, I'm making sure that any protection or unprotection of sheets only happens on the currently selected sheet.

 

[KSE]

I have the same problem with several spreadsheets and macros I used for years now. The info to select each sheet works great. Thanks!

 

[JFB]

All I can say is ditto.

I used to manually select my sheets to clear the problem but the code previously provided automates this process.

All I would say is that the code is worth tweeking, as follows:


Dim i As Integer
Dim strWorksheet As String
strWorksheet = ActiveSheet.Name
For i = 1 To Worksheets.Count

If Worksheets(i).Visible Then

Worksheets(i).Select

End If

Next

Worksheets(strWorksheet).Select


The 'if visible' test is required as Excel will throw an error if you try to select a hidden sheet.

I have removed the Worksheets(i).Protect line as this isn't required to cure the problem, indeed I use the userInterfaceOnly option and running .Protect against all sheets would undo this.

Placing this as the last bit of code in the: 'Workbook_Open' area of 'ThisWorkbook' is also helpful, as the workaround can be removed with ease should the bug be fixed by Microsoft.

Hope this helps (as this thread as helped me - for I while I thought it was only me)

JFB

 

[bisel]

Wow, at last I have found others with this same exact problem in MS Excel 2003. I too have a large workbook with many worksheets. I have found two workarounds.

First ... I created a macro with a single entry ... I assigned a shortcut and when the screen paint problem occurs, I run the macro.

Sub screenupdate()

Application.ScreenUpdating = True

End Sub
​

This corrects the display ... but only once. So, not a permanent fix. I also inserted the line in my other macros to run prior to ending the sub and that sometimes fixes the problem ... but is pain.

I found another case using Google ... there is a folder called:
C:\Documents and Settings\%username%\Application Data\Microsoft\Excel

If you delete the contents of that folder, the problem goes away, but you will lose your customized settings for toolbars. Unfortunately, I found the problem comes back ... so that too is not a permanent solution.

I surely would appreciate it if some one has a more permanent fix for this anomaly.

Steve

 

[JFB]

This problem seems to be fixed - can anyone else confirm this?

Cheers

JFB