Use VBA to format a pivot chart so only one series displays

[Legacy 139986]

I have an interactive pivot chart that allows users to select a region from one drop down list then a product based on that region from another drop down list. The line chart displays two series that are a percentage of a whole. (i.e., what percent of stock is blue and what percent is red) then graphs the % over time showing the trend of one color over the other. I only want to display the trend of the blue %. I tried filtering the chart to only display the blue but it showed blue at 100% (I'm guessing that's because I removed the second half of the equation). So, I'm trying to figure out how to create VBA code that will format the chart by setting the marker and line for the red series to 'None' but leave the blue. When I record a macro setting the marker and line of the red series to 'none' only code for removing the marker shows up in the code. Another problem is that sometimes the macro will wipe out the blue series, too. As part of my recorded macro I selected a marker and line style for the blue series and added that to my previous code but this only makes the markers display, still no lines. How do I write code for lines not to display in one series but display in the other?

 

[sijpie]

Excel 2007+? Because older versions use graphs differently in code.

 

[Legacy 139986]

Yes, 2007. Thanks.

 

[sijpie]

Here is a sub that will do the trick with a little test sub that shows how to use it

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#007F00">'-----------------------------------------------------------</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> Hide_Show_Line(iLine <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, bShow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br><SPAN style="color:#007F00">'  Macro to hide or show line on graph.</SPAN><br><SPAN style="color:#007F00">'  The 1st parameter passed is for the line to show or hide</SPAN><br><SPAN style="color:#007F00">'  the 2nd parameter is True if you want to show line</SPAN><br><SPAN style="color:#007F00">'  and False if you want to hide line</SPAN><br>    <br><SPAN style="color:#007F00">'-----------------------------------------------------------</SPAN><br><br>    ActiveSheet.ChartObjects("Chart 1").Activate<br>    <SPAN style="color:#00007F">With</SPAN> ActiveChart<br>        <SPAN style="color:#00007F">If</SPAN> iLine < 1 <SPAN style="color:#00007F">Or</SPAN> iLine > .SeriesCollection.Count <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "Invalid line number. Please provide number " _<br>                    & "between 1 and " & .SeriesCollection.Count<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#00007F">With</SPAN> .SeriesCollection(iLine).Format.Line<br>            <SPAN style="color:#00007F">If</SPAN> bShow <SPAN style="color:#00007F">Then</SPAN>       <SPAN style="color:#007F00">' show line</SPAN><br>            .Visible = msoTrue<br>            <SPAN style="color:#00007F">Else</SPAN><br>            .Visible = msoFalse<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Sub</SPAN> shtest()<br>    Hide_Show_Line 2, <SPAN style="color:#00007F">False</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Legacy 139986]

Oh, thank you!! All the research I did confirmed that you can't record a macro to do that specific piece and said I 'had to figure it out'. Of course, 'figuring it out' was what I needed help with. You did that for me. Thanks again.