none
Custom chart templates and color themes in Excel 2010

    Question

  • We have created a bunch of custom chart templates for our users. We also have an application where we need to change the color for all the charts in a workbook. In Excel 2003 this was easy, change the palette and the charts have to change to the new colors. In 2010, it looks like it's easy but it doesn't work as one might expect

    All the chart templates were created using the active color theme. When we apply one to the chart the chart takes on the colors that were active when the template was created. Changing the color theme for the workbook does nothing to the color in the chart with the custom template applied.

    We can choose Reset to match style for the chart and now the colors change with the theme. However, that will also change any fonts, lines, legends, etc that may have been set up in the custom template.

    Why doesn't this work? One would expect that if you are creating charts that you should be able to change the colors of all charts with a color theme change. Is this a bug?

    Our problem is that the user may not know what color theme they need until quite late in the process of preparing their report. We prefer that they use our custom template but then they can't change the color once they know which one they need. We thought of creating each of our templates in each of the color themes we have but that would amount to over 300 templates. And to expect the user to reapply templates when they may have 50 charts they created is a bit unreasonable.

    An interesting workaround is to reset the style of the chart which removes all the custom formatting, then press Ctrl-Z to undo the reset. The fonts and other formatting come back but now the color will change with the theme. It's weird that this works which makes it seem almost certainly that we have encountered a bug. We thought that we could automate this so the user can reset the chart with a single button click. But it turns out that you can't undo the VBA command (you can do this in Word, don't know why Excel doesn't do it). I have found some code out there that tries to save the state of whatever is being changed so it can be restored. But that's not what we need. We need VBA to perform an action and then undo the action.

    Monday, February 25, 2013 11:13 PM

Answers

  • You have clearly described the default behaviour! No doubt that's how some prefer it, to permanently set a custom chart's colours to the given color-scheme, but for sure others would prefer it works as you would.

    Instead of saving as a chart template have you tried saving your custom charts in an ordinary workbook, myChartTemplates.xlsm. Set all the formats but don't apply fixed RGB colours.

    When you want use or apply custom chart, you can copy the chart from the file to your working file and work with that. Alternatively make a new chart, copy the custom chart, and paste-special formats onto the new chart. Colours should update to the current theme. You can educate your users to do that manually, or with code.

    One thing though, whenever VBA changes the interface, cells, formats, charts, etc, normal undo is irrevocably lost. That said you can store the before, and assign a macro which will get called with Application.OnUndo (see OnUndo in help).

    Peter Thornton

    Tuesday, February 26, 2013 6:02 PM

All replies

  • You have clearly described the default behaviour! No doubt that's how some prefer it, to permanently set a custom chart's colours to the given color-scheme, but for sure others would prefer it works as you would.

    Instead of saving as a chart template have you tried saving your custom charts in an ordinary workbook, myChartTemplates.xlsm. Set all the formats but don't apply fixed RGB colours.

    When you want use or apply custom chart, you can copy the chart from the file to your working file and work with that. Alternatively make a new chart, copy the custom chart, and paste-special formats onto the new chart. Colours should update to the current theme. You can educate your users to do that manually, or with code.

    One thing though, whenever VBA changes the interface, cells, formats, charts, etc, normal undo is irrevocably lost. That said you can store the before, and assign a macro which will get called with Application.OnUndo (see OnUndo in help).

    Peter Thornton

    Tuesday, February 26, 2013 6:02 PM
  • Thank you Peter! This is very helpful. I can't imagine why someone would want to use a custom chart but not be able to change color with a theme change but I guess some do. For our purposes however, we do need to have the color change. I've tested copying and pasting just the format and we can indeed change colors according to the theme. We'll have to write some code to make it easier for users to apply the charts the correct way but that is doable.

    Kathy Koppinger

    Wednesday, February 27, 2013 2:13 PM