Troubleshooting a Worksheet Graphic
EE Pros,
Happy Holiday!
I have a Prioritization graphic that several EE Pros have helped me with. Here's the issue. I have copied it from a major WB to make it an individual WS in its own WB. I've embedded the correct Macros that make it work and there are no Private Sub statements. HOWEVER, when I begin to fill it out (B5 - E14), the Graphic does some funky things. It disappears and then reappears. I've looked over the Macros, the formulas, the Range Names and cannot find what makes this graphic not behave.
So what do I mean by behave? Simply stay put and update itself as the selections in the cells are made.
To try it, simply add Text in B5, move to C5 - E5 and add in something from the drop down boxes. You will see the bad behavior.
Thank you in advance.
B.
Priority-Map.xlsm
Happy Holiday!
I have a Prioritization graphic that several EE Pros have helped me with. Here's the issue. I have copied it from a major WB to make it an individual WS in its own WB. I've embedded the correct Macros that make it work and there are no Private Sub statements. HOWEVER, when I begin to fill it out (B5 - E14), the Graphic does some funky things. It disappears and then reappears. I've looked over the Macros, the formulas, the Range Names and cannot find what makes this graphic not behave.
So what do I mean by behave? Simply stay put and update itself as the selections in the cells are made.
To try it, simply add Text in B5, move to C5 - E5 and add in something from the drop down boxes. You will see the bad behavior.
Thank you in advance.
B.
Priority-Map.xlsm
ASKER
Serena, Thanks for taking a look. I deleted the single link which is not necessary or part of the standalone Worksheet/WB. I still have the same issue. Something is hanging up the graphic; particularly on the first row. When I fill out the second row, it updates immediately. Also when I clear the sheet with the button, I get the same stagnate response. Something is going on but I cannot figure it out.
B.
B.
I am not reproducing the problem either. I tested in 64-bit Excel 2016. Which version are you using?
Also, I noticed that series 7, 8 & 9 on the chart have an empty string constant assigned to the X-values (instead of W68:W70). You can see this by selecting the chart and clicking the Select Data menu item. Scroll down to select one of those series, then click on the Edit button in the Legend Entries (Series) pane at the left.
I also noticed that you only have 9 series in the chart--but 10 rows of values to populate them with (U62:Y71).
Also, I noticed that series 7, 8 & 9 on the chart have an empty string constant assigned to the X-values (instead of W68:W70). You can see this by selecting the chart and clicking the Select Data menu item. Scroll down to select one of those series, then click on the Edit button in the Legend Entries (Series) pane at the left.
I also noticed that you only have 9 series in the chart--but 10 rows of values to populate them with (U62:Y71).
I tested 32-bit Excel 2007, 2010, 2013 and 2016. I reproduce funky behavior with Excel 2007 and 2010, with the gradient fill being absent at first and progressively changing from gray to black as data are entered. Once all the data are entered, the chart finally gets its gradient fill.
Excel 2013 and 2016 behave like the gentlemen they are.
FWIW, Microsoft changed to the Ivy chart engine with Excel 2013. Even more changes were made with Excel 2016. Among other things, Microsoft made it very convenient to add a rectangular gradient fill in the plot area. I suspect that the chart was made with the new chart engine, and then you had problems with it in the older chart engine.
Rather than trying to use the problematic gradient fill in the older versions of Excel, you might consider using a picture fill instead. Make (or find) a high-quality gradient scale with colors you like, then use that as a picture fill rather than the gradient fill you have now. The attached file uses that approach.
Priority-MapQ28991425x.xlsm
Excel 2013 and 2016 behave like the gentlemen they are.
FWIW, Microsoft changed to the Ivy chart engine with Excel 2013. Even more changes were made with Excel 2016. Among other things, Microsoft made it very convenient to add a rectangular gradient fill in the plot area. I suspect that the chart was made with the new chart engine, and then you had problems with it in the older chart engine.
Rather than trying to use the problematic gradient fill in the older versions of Excel, you might consider using a picture fill instead. Make (or find) a high-quality gradient scale with colors you like, then use that as a picture fill rather than the gradient fill you have now. The attached file uses that approach.
Priority-MapQ28991425x.xlsm
After sleeping on your problem, I realized your chart was in a funk because its autoscaled axes were thinking about the lack of data too much. The fix to the funk is changing your chart to use fixed axes (I used -0.2 to +2.2) while also changing the formulas in U62:X71 so they return X,Y co-ordinates of -1.0 and bubble size of 0.01 if the task is blank. In so doing, the chart knows exactly where to plot the nine series when the user hasn't entered any data yet--off the chart, below and to the left of the plot area. When you do this, the rectangular color gradient appears in all its glory.
This approach works in both Excel 2007 (32-bit) and Excel 2016 (64-bit).
Priority-Map.xlsm
This approach works in both Excel 2007 (32-bit) and Excel 2016 (64-bit).
Priority-Map.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brad, "Brilliant"!!! Thank you very much. Works perfectly! I hope it was at least entertaining for you to rattle this around in your brain during periods of sleep ;-) I think we are made from the same cloth. Much thanks.
B.
B.
ASKER
Brad, "Brilliant"!!! Thank you very much. Works perfectly! I hope it was at least entertaining for you to rattle this around in your brain during periods of sleep ;-) I think we are made from the same cloth. Much thanks.
B.
B.
The workbook contains one or more links that cannot be updated.
And then, when checking the links, it is looking for a file called: IOT Workbench v1.xlsm
After breaking the link, I input values in the first row and randomly selected options for C (very high), D (somewhat), and E (reasonable) from the drop-downs. A large ball appears in the chart with a 1 on it in the upper left corner.