SOLVED

Showing gray bar when opening the excel and lags when scrolling the window

%3CLINGO-SUB%20id%3D%22lingo-sub-1495109%22%20slang%3D%22en-US%22%3EShowing%20gray%20bar%20when%20opening%20the%20excel%20and%20lags%20when%20scrolling%20the%20window%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495109%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3A%3C%2FP%3E%3CP%3EMy%20excel%20version%20is%20Office%20365%20Proplus%2C%201902%20version.%3C%2FP%3E%3CP%3EI%20use%20some%20macros%20in%20these%20charts%20but%20the%20total%20file%20size%20is%20about%20800k.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20encounter%202%20issues%20and%20it%20seriously%20affect%20the%20working%20efficiency.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20The%20chart%20shows%20gray%20bar%20every%20time%20when%20opening%20the%20excel%20file.%3C%2FP%3E%3CP%3ELike%20the%20pictures%20I%20show%20below.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Gray%20bar.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201792i621DF3E731843F14%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Gray%20bar.png%22%20alt%3D%22Gray%20bar.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E2.%20Every%20time%20when%20I%20scroll%20the%20windows%20the%20as%20long%20as%20it%20touch%20the%20chart%20then%20lags%203-5s%20and%20the%20chart%20will%20appear%20gradually.%20Please%20refer%20to%20the%20following%20gif.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22scroll%20lag.gif%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201793i27B1F098C454344C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22scroll%20lag.gif%22%20alt%3D%22scroll%20lag.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBoth%20symptoms%20just%20appear%20in%20the%20version%20after%20office%202016%2C%20because%20I%20can't%20reproduce%20them%20in%20Office%202013.%20I%20think%20it%20must%20some%20new%20features%20affect%20in%20the%20new%20version%20after%20Office%202016.%3C%2FP%3E%3CP%3EIs%20there%20any%20approach%20to%20resolve%20these%202%20symptoms%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much.%3C%2FP%3E%3CP%3EZac%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1495109%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495894%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20gray%20bar%20when%20opening%20the%20excel%20and%20lags%20when%20scrolling%20the%20window%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495894%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712541%22%20target%3D%22_blank%22%3E%40Zac_Chen2020%3C%2FA%3E%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EPoint%2001%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EYou%20need%20to%20debug%20you%20code%20and%20see%20what%20is%20making%20this%20mess%20%26amp%3B%20slight%20alter%20the%20code%20accordingly.%20What%20I%20can%20understand%20it%20is%20not%20finding%20the%20range%20of%20the%20data%20source..%20It%20will%20be%20difficult%20to%20comment%20until%20not%20debug%20and%20see%20the%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EPoint%2002%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EYou%20need%20to%20adjust%20the%20chart%20settings%20by%20selecting%20the%20chart%20%26amp%3B%20right%20click%20you%20see%20the%20format%20chart%20area%20%26gt%3B%20select%20%22Don't%20move%20or%20size%20with%20cells%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_e81ae2.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201841iE0357818D63FEC9A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Snag_e81ae2.png%22%20alt%3D%22Snag_e81ae2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Regular Visitor

Hi:

My excel version is Office 365 Proplus, 1902 version.

I use some macros in these charts but the total file size is about 800k. 

I encounter 2 issues and it seriously affect the working efficiency.

 

1. The chart shows gray bar every time when opening the excel file.

Like the pictures I show below. 

Gray bar.png

2. Every time when I scroll the windows the as long as it touch the chart then lags 3-5s and the chart will appear gradually. Please refer to the following gif.

scroll lag.gif

 

Both symptoms just appear in the version after office 2016, because I can't reproduce them in Office 2013. I think it must some new features affect in the new version after Office 2016.

Is there any approach to resolve these 2 symptoms?

 

Thank you so much.

Zac

 

1 Reply
Highlighted
Best Response confirmed by Zac_Chen2020 (Regular Visitor)
Solution

Hi @Zac_Chen2020;

 

Point 01

You need to debug you code and see what is making this mess & slight alter the code accordingly. What I can understand it is not finding the range of the data source.. It will be difficult to comment until not debug and see the code.

 

Point 02

You need to adjust the chart settings by selecting the chart & right click you see the format chart area > select "Don't move or size with cells"

 

Snag_e81ae2.png

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more