how to toggle the 'visible' property of a series

Yes, to my knowledge there's no such thing as the 'visible' property of a series.

But I needed to be able to quickly hide/show series on a chart sheet and did not want to futz with hiding columns etc (vba too involved, didn't want to have to create a form, multiple workbooks involved, etc.) so I came up with the following system of subs.

sub ToggleVisibleSeries builds the inputbox where one toggles the parameters for an individual series to show (1) or be hidden (0). At first the input box can look daunting but it is a quick way of providing an interface to the 'visible' parameter of the series

sub DefineSeriesCollection creates a "local" index for each series by assigning each successive series a factor of ten higher index (i.e. Series 1 gets local index 1, Series 2 gets local index 10, Series 3 gets local index 100 and so on) and then adds these to form a global index for all series which could look like 111111 if you have 6 series. This concatenated local index is the default input for the inputbox

Note: I defined a redim of the vSeriesVisibleFlagCollection array contained in this sub with the workbook_open event as follows

Please Login or Register  to view this content.

sub ToggleSeriesYValues kicks off the toggling of the Y values for a series to 0 to hide it or to its original values to show it by using the replace function to modify the series formula

sub ToggleSeriesVisibleFlag actually does the toggling of the 'visible' flag

Below is the code (which when run will hopefully be better explain itself than this text). Just paste this to a module and fire off ToggleVisibleSeries and change the inputs from 1 to 0 and back to watch your series show or be hidden

HERE'S THE QUO: can you tell me how to make the four subs better?

Thanks All!

Please Login or Register  to view this content.

Hi there,

Maybe I've misunderstood what you're up to, but this seems SERIOUSLY more complicated than the VBA code for hiding rows & columns to hide the display of a series on a graph!

Would you like me to take a look at your workbook to see if I can propose a more simple solution?

Regards,

Greg M

Greg M,

I read of your offer to assist me with VBA code to hide/show columns in order to toggle series from viewable to hidden. As my post implies I specifically wanted to avoid this approach because of many factors. From my original post

But I needed to be able to quickly hide/show series on a chart sheet and did not want to futz with hiding columns etc (VBA too involved, didn't want to have to create a form, multiple workbooks involved, etc.) so I came up with the following system of subs.

Maybe my first statement that VBA is too invloved may not be strictly correct but the follow on statements about not wanting to create a form, multiple workbooks involved really do impact my decision to attempt this with a 'much more complex approach.' Take the 'no form' requirement: I have sidestepped the need to create a form by, in effect, substituting the form with a simple dialog box generated at 'run time'.