Chart to keep its set formatting and not change back to 'automatic format'.

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hi guys

    I've got a data table and a graph is plotted from it. Using checkboxes (form control, not active X) I can temporarily remove the chart's series via VB code (e.g. 'if checkbox is true, then hide this row' type of thing) as this makes the chart's legend dynamic. The graph is your typical xy graph (two y-axis and one x-axis).

    As far as functionality everything is working as it should but the problem lies in line/marker formatting on the graph. If I hide/unhide rows (and thus series) the graph format is lost - it goes back to automatic (changes to a different colour or adopts new markers), starts missing markers or line.

    Not sure what's going on here, since I have done the same project before and everything works fine. Any ideas?

    Many thanks.

    Also posted here:…o_automatic_format_after/

  • Re: Chart to keep its set formatting and not change back to 'automatic format'.

    Thanks for the suggestion pike.

    I have finally managed to figure it out... When I opened up 'Select Data' then went to 'Hidden and Empty Cells' - I've had 'Show data in hidden rows and columns' unchecked.

    So when I would hide a series (a row with series data in it) with a macro, Excel would lose it's set formatting and 'cycle' through automatic or formatting for other graphs.

    When I checked the option to show data in hidden rows and columns I was still able to hide the series but keep the formatting. :cheers:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!