Jul 08 2021 02:10 PM - edited Jul 08 2021 02:20 PM
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
Jul 09 2021 06:19 AM
Jul 09 2021 11:31 AM - edited Jul 09 2021 11:35 AM
- 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
Jul 09 2021 01:23 PM
Jul 10 2021 10:42 AM
Jul 12 2021 12:59 AM
@Matt12032 Excellent. Sorted then.
May 11 2023 11:14 AM
@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.
May 12 2023 02:39 AM
@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