excel screen partially corrupts

demac43 · December 4th, 2006, 07:06 PM

Hi,

This is quite strange. Every now and then and without warning, I have a section of the screen that blanks out. It has the 3D button windows color and is placed on the LHS of the screen. It covers 3 columns and about 42 rows. It's top left corner begins at the intersection of row and column headers.

It only affects the current excel project which turns off commandbars, sheet tabs and scroll bars. Sheets are unprotected.

I did at one stage use code to expand the name box width. The blank area starts just below the name box. Maybe it's related to that.

I wonder if anyone else has had a similar experience or could make a suggestion?

regards Dennis

maccas · December 5th, 2006, 04:41 AM

I've had simlar problems before on numerous occassions (although not the specific problem you describe). I think its not so much a problem with Excel as Windows itself, the two get confused and Windows decides that it doesn't need to repaint Excel - helpfully enough.

If the problem is occuring predictably then you can use some code with WinAPI calls to force Windows to start to repaint Excel (see code extract below). If its just happening intermittently then you may be best off just sorting it out manually - scrolling around or minimising & maximising often does the trick. NB I've used the code in the past to refresh ActiveX objects and not the Excel main window but there should be no reason why the same should not work - I hope!

Maccas

Code:

' Code was catually written by the very useful Stephen Bullen.
' If you're interested in the background to this have a look at:
'
' http://groups.google.co.uk/groups?hl...e=UTF-8&thread
' m=375FD45C.7CA1AAC0%40envantage.com&rnum=7&prev=/groups%3Fq%3DListBo
' x%2Bscreenupdating%2Brefresh%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DU
' TF-8%26selm%3D375FD45C.7CA1AAC0%2540envantage.com%26rnum%3D7
'
' The routine below uses API calls to force windows to re-draw the active
' window, thereby refreshing troublesome ActiveX controls

Declare Function FindWindow _
    Lib "user32" _
    Alias "FindWindowA" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) _
    As Long

Declare Function FindWindowEx _
    Lib "user32" _
    Alias "FindWindowExA" ( _
    ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, _
    ByVal lpsz2 As String) _
    As Long

Declare Function InvalidateRect _
    Lib "user32" ( _
    ByVal hWnd As Long, _
    lpRect As Long, _
    ByVal bErase As Long) _
    As Long

Declare Function UpdateWindow _
    Lib "user32" ( _
    ByVal hWnd As Long) _
    As Long

Sub RepaintActiveWindow(z As Byte)

Dim hWnd As Long
Dim strExcel As String

    strExcel = "EXCEL" & Format(Application.Version, "#0")

    Application.ScreenUpdating = True

    ' Find Excel
    hWnd = FindWindow("XLMAIN", Application.Caption)

    ' Find the active window in Excel
    hWnd = FindWindowEx(hWnd, 0, strExcel, ActiveWindow.Caption)

    ' Mark the client area 'dirty'
    InvalidateRect hWnd, 0&, True

    ' Force a redraw
    UpdateWindow hWnd

End Sub

demac43 · December 5th, 2006, 01:31 PM

thanks maccas,

It acts a bit like an object itself and prevents any mouse access to the cells & buttons there. I've tried all standard screen methods to remove it but no go.

It's there on every sheet, and remains after closing the workbook and seems to me now to be generated by excel itself (and/or winXP)and i have to close and restart excel to be rid of it.

Last time it appeared was at the same instant that I loaded a second workbook (an earlier version of the current project).

I'll put this code behind a button and test it out the next time it appears.

thanks again for your time and effort - Regards Dennis

tepmoc · December 12th, 2006, 10:28 AM

The manual fix is to drag one of the menus into the section. I am still looking for a VBA fix.

tepmoc · December 12th, 2006, 10:43 AM

post this at the end of your VBA code:

    Dim mPos As Variant
    mPos = Application.CommandBars.Item(1).Position
    Application.CommandBars.Item(1).Position = msoBarLeft
    Application.CommandBars.Item(1).Position = mPos