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

%3CLINGO-SUB%20id%3D%22lingo-sub-2530128%22%20slang%3D%22en-US%22%3EExcel%20Display%20Issue%20When%20Working%20w%2F%20Multiple%20Sheet%20%26amp%3B%20Macro%20Enabled%20Woorkbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2530128%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%2039%20different%20worksheets%20in%20one%20excel%20file%20and%20I%20have%20simple%20VBA%20codes%20that%20basically%20hides%2Funhides%20or%20protects%20chosen%20worksheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20try%20to%20calculate%20one%20formula%20(Basically%20change%20one%20of%20the%20parameters%20in%20the%20worksheet%20that%20results%20in%20excel%20to%20initiate%20calculation)%2C%20an%20arbitrary%20excel%20worksheet%20seems%20to%20be%20tangled%20with%20the%20current%20worksheet%20like%20the%20picture%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20scroll%20away%20or%20change%20the%20worksheet%20and%20then%20comeback%2C%20the%20worksheet%20view%20becomes%20normal.%20Every%20time%20a%20calculation%20takes%20place%20in%20one%20of%20those%20worksheets%2C%20another%20arbitrary%20worksheet%20in%20the%20workbook%20distorts%20the%20view.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20save%20the%20file%20as%20Excel%20Workbook%2C%20that%20is%20without%20macro-enabled%2C%20the%20problem%20disappears.%20But%20the%20thing%20is%20this%20view%20problem%20does%20not%20happen%20when%20I%20activate%20of%20the%20codes.%20So%20I%20fail%20to%20see%20how%20this%20is%20related%20to%20VBA%20side.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomeone%20once%20suggested%20me%20to%20disable%20hardware%20graphics%20acceleration.%20I%20did%20it%20but%20still%20no%20luck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3EMatt%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETangled%20View%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Matt12032_2-1625778922692.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294500iE82069BCB051A789%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Matt12032_2-1625778922692.png%22%20alt%3D%22Matt12032_2-1625778922692.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOriginal%20View%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Matt12032_3-1625778935288.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294501i3A85787C6468A1DD%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Matt12032_3-1625778935288.png%22%20alt%3D%22Matt12032_3-1625778935288.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2530128%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2531963%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Display%20Issue%20When%20Working%20w%2F%20Multiple%20Sheet%20%26amp%3B%20Macro%20Enabled%20Woorkbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2531963%22%20slang%3D%22en-US%22%3EYou%20say%20that%20the%20problem%20does%20not%20happen%20if%20you%20save%20the%20file%20as%20xlsx%20but%20you%20also%20say%20you%20fail%20to%20see%20that%20the%20problem%20is%20related%20to%20the%20VBA%20code.%20That%20is%20a%20contradiction%20if%20you%20ask%20me.%3CBR%20%2F%3EAre%20there%20any%20used%20defined%20functions%20involved%20(VBA%20functions%20called%20from%20a%20worksheet%20cell)%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2533199%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Display%20Issue%20When%20Working%20w%2F%20Multiple%20Sheet%20%26amp%3B%20Macro%20Enabled%20Woorkbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2533199%22%20slang%3D%22en-US%22%3E%3CP%3E-%20I%20understook%20what%20you%20mean%20and%20you%20are%20right%20about%20the%20contradiction.%3CBR%20%2F%3E-%20No%2C%20there%20are%20no%20UDF%20involved.%3CBR%20%2F%3E-%20What%20I%20found%20is%20that%20if%20I%20disable%20the%20following%20alerts%20for%20every%20sheet%20with%20the%20code%20below%2C%20the%20display%20issue%20appears%20and%20then%20disappears%20shortly%2C%20which%20ultimately%20solves%20the%20problem.%20BUT%20I%20do%20not%20want%20to%20see%20that%20tangled%20vision%20at%20all.%3CBR%20%2F%3E-%20My%20assumption%20is%20that%20as%20there%20are%20lots%20of%20tabs%20with%20multiple%20formulas%20involved%2C%20whenever%20I%20change%20a%20parameter%20within%20one%20tab%20(Which%20has%20no%20effect%20on%20any%20VBA%20code)%2C%20Excel%20automatically%20calculates%20the%20whole%2040%20tabs%20(Even%20that%20change%20has%20no%20relation%20on%20the%20tab%20that%20is%20being%20calculated)%20from%20scratch%2C%20resulting%20a%20graphical%20problem%2C%20unable%20to%20refresh%20the%20current%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20made%20myself%20clear.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3EApplication.DisplayAlerts%20%3D%20False%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EApplication.AlertBeforeOverwriting%20%3D%20False%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EApplication.ScreenUpdating%20%3D%20False%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3EEnd%20Sub%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2533549%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Display%20Issue%20When%20Working%20w%2F%20Multiple%20Sheet%20%26amp%3B%20Macro%20Enabled%20Woorkbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2533549%22%20slang%3D%22en-US%22%3EIs%20that%20all%20the%20code%20in%20that%20routine%3F%20If%20I%20could%20see%20the%20file%20I%20might%20be%20able%20to%20find%20out%20what%20causes%20the%20problem%3C%2FLINGO-BODY%3E
New Contributor

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

Matt12032_1-1625779210354.png

 

 

 

Original View

Matt12032_0-1625779103063.png

 

 

 

 

5 Replies
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)?

- 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

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
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.