Excel Display Issue When Working w/ Multiple Sheet & Macro Enabled Woorkbook

[Matt12032]

Hi,

I have 39 different worksheets in one excel file and I have simple VBA codes that basically hides/unhides or protects chosen worksheets.

When I try to calculate one formula (Basically change one of the parameters in the worksheet that results in excel to initiate calculation), an arbitrary excel worksheet seems to be tangled with the current worksheet like the picture below.

When I scroll away or change the worksheet and then comeback, the worksheet view becomes normal. Every time a calculation takes place in one of those worksheets, another arbitrary worksheet in the workbook distorts the view.

When I save the file as Excel Workbook, that is without macro-enabled, the problem disappears. But the thing is this view problem does not happen when I activate of the codes. So I fail to see how this is related to VBA side.

Someone once suggested me to disable hardware graphics acceleration. I did it but still no luck.

Any suggestions?

Thanks in advance,

Matt

Tangled View

Original View

[#2]

You say that the problem does not happen if you save the file as xlsx but you also say you fail to see that the problem is related to the VBA code. That is a contradiction if you ask me.
Are there any used defined functions involved (VBA functions called from a worksheet cell)?

[#3]

- I understook what you mean and you are right about the contradiction.
- No, there are no UDF involved.
- What I found is that if I disable the following alerts for every sheet with the code below, the display issue appears and then disappears shortly, which ultimately solves the problem. BUT I do not want to see that tangled vision at all.
- My assumption is that as there are lots of tabs with multiple formulas involved, whenever I change a parameter within one tab (Which has no effect on any VBA code), Excel automatically calculates the whole 40 tabs (Even that change has no relation on the tab that is being calculated) from scratch, resulting a graphical problem, unable to refresh the current tab.

Hope I made myself clear.


Private Sub Worksheet_Change(ByVal Target As Range)

Application.DisplayAlerts = False
Application.AlertBeforeOverwriting = False
Application.ScreenUpdating = False

End Sub

[#4]

Is that all the code in that routine? If I could see the file I might be able to find out what causes the problem

[#5]

I solved the problem. For every VBA code that I have & within ThisWorkbook/WorkSheetChange Module, I disabled ScreenUpdating, AlertBeforeOverwriting & DisplayAlerts by using the code I showed before. With this way, the distortion disappeared. Thanks for your time.

[#6]

@Matt12032 Excellent. Sorted then.

[#7]

@Jan Karel Pieterse Hey there, I have been searching the internet left right and center to find a solution to my issue. I noticed you helped a member solve a similar issue so fingers crossed I can get the same thing working with my excel workbook. Basically I have a similar situation (many sheets, lots of formulas (all cells shaded blue which are either simple mathematical formulas or some sort of lookup formula)). What I have done is make a few buttons on each page which essentially add or remove pages so that the user doesn't have to remember passwords and unlock the workbook to unhide or hide sheets. My issue is that when these simple macros are enabled I get screen display issues like the previous user. On some sheets I will open it up by clicking the button and the sheet will look normal but when I input any data all of a sudden the sheet becomes see through on some cells. I can scroll down and then back up and the display error is fixed but then when I input data again the same issue keeps happening. It only seems to happen on sheets where other pages are referencing or looking up cells that are on that sheet. Please see the photos for a better Idea.

[#8]

@Jn12345 You could add code that does the scroll down and back up when the actual work is finished:

Sub ScrollUpAndDown()
    Dim rws As Long
    rws = ActiveWindow.VisibleRange.Rows.Count
    ActiveWindow.SmallScroll rws
    ActiveWindow.SmallScroll , rws
End Sub