Macro to Toggle the Display of Vertical Lines on a Custom Combination Chart

[FrankNJohnson]

Friends:


To my recollection, this is my first time posting here at MrExcel.com, although I can't count the number of times the forums have helped me! I do pretty well with Excel formulas, but I'm pretty much a novice when it comes to macros. I can cobble together working macros by doing a lot of online searches and with a lot of trial and error, but writing a macro from scratch is definitely not my strong suit.


With that as background, I have a question about a macro I'm trying to create that will toggle the display of vertical lines on a custom combination chart I want to use for reporting ecommerce data. I used this tutorial:


https://www.myexcelonline.com/blog/interactive-vertical-column-in-your-excel-line-chart/



for much of what I did to create the chart itself.


I have my data in eight columns (some of this probably doesn't matter for my question, but for the sake of completeness and because it's easier for me to describe my data and chart if I'm methodical):


Column A: Date - self-explanatory
Column B: Day of Year - 1 through 365; I found I needed to do this to make what I'm calling the "Help Line" work (this is what the tutorial above calls an "Interactive Vertical Column")
Column C: Visits - website visits
Column D: Help Line Formula - this is where I put the formula described in the tutorial above
Column E: Help Line Position - this column has the cell link for the scrollbar described in the tutorial above
Column F: Catalog Drops - in this column, if I add 100 to a cell, a vertical line is created on the chart corresponding to the date in Column A of the same row
Column G: Email Blasts - same functionality as in Column F
Column H: Astronomical Events - same functionality as in Column F


I then have a custom combination chart which is meant to display the Visits data as a line chart. I want to show the effect of various events on ecommerce performance, so I am using the data I enter into Columns F, G, and H to display vertical lines on top of the line chart. I also have the "Help Line" from the tutorial above which I can make appear on the chart through use of the scrollbar. The data series which create the vertical lines (from the data in Columns F, G, and H and for the Help Line are set to the clustered column chart type and use the secondary axis).


I have three macros that I want to use to toggle the display of the vertical lines created by the data in Columns F, G, and H. Here is an example of one of those macros:

Sub ToggleCatalogDrops()
'
' ToggleCatalogDrops Macro
'


    Dim cht As Chart
    Dim ser As Series


    'Retrieve our chart and seriescollection objects'
    Set cht = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
    Set ser = cht.SeriesCollection(3)


    'Set the first series line to be hidden'


    With ser.Format.Line
        If .Visible = msoTrue Then
            .Visible = msoFalse
        Else
            .Visible = msoTrue
        End If
    End With


End Sub

At first this seemed to be working, but before long, instead of hiding the vertical lines, the macro changed the lines to include a 0.75pt black border (this is also happening on the Help Line when I use the scrollbar to add it to the chart). Here are some screenshots that demonstrate the issue:


http://www.franknjohnson.net/misc/mrexcel-ss1.jpg - the chart with the catalog drop green lines in their normal state
http://www.franknjohnson.net/misc/mrexcel-ss1a.jpg - the Format Data Point settings for the catalog drop green lines in their normal state
http://www.franknjohnson.net/misc/mrexcel-ss2.jpg - the chart with the catalog drop green lines after I have run the macro (with the 0.75pt black border)
http://www.franknjohnson.net/misc/mrexcel-ss2a.jpg - the Format Data Point settings for the catalog drop green lines after I have run the macro

I've also put my Excel file (including the macro) here, in case it helps:
http://www.franknjohnson.net/misc/vertical-line-test.zip


I have exhausted my ability to search for solutions, and I'm hoping someone here on the forums will have an idea for me. Thanks in advance for any help anyone can offer - it's much appreciated!


Frank