Using msoTrue in VBA to change series marker visibility not working properly.

[vris92]

<form action="https://www.reddit.com/r/excel/comments/5h19fx/using_msotrue_in_vba_to_change_series_marker/#" class="usertext warn-on-unload" id="form-t3_5h19fxqoc" style="margin: 0px; padding: 0px; font-size: small;">I have an Excel 2016 project I am working on and have need of a check box to toggle the visibility of a data series on a chart. The code I have included below works fine to hide the data series when I uncheck the box, but when I check the box again, the fill is not restored. Is there any way that I can affect this through the checkbox interface?


<code style="font-family: 'Courier New', courier, monospace; margin: 0px 2px; padding: 15px; border: 0px; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 16px; overflow: auto; background-color: transparent;">Sub CheckBox17_Click()
Dim boxvalue As Long
boxvalue = Worksheets("Test View").Shapes("Check Box 17").ControlFormat.Value
If boxvalue = 1 Then
Worksheets("Test View").ChartObjects("Chart 2").Chart.FullSeriesCollection(2).Select
Selection.Format.Fill.Visible = msoTrue
Else
Worksheets("Test View").ChartObjects("Chart 2").Chart.FullSeriesCollection(2).Select
Selection.Format.Fill.Visible = msoFalse
End If
End Sub
</code>



</form>
Thanks for any insight you might have guys.

 

[dmt32]

Hi welcome to forum.

Just a guess but try replacing msoTrue with msoCTrue & see if that helps you.

Dave

 

[vris92]

Hi Dave, thanks. Unfortunately that doesn't seem to change anything It still turns off fill just fine with msoCFalse instead of msoFalse, but it won't turn it back on. For the life of me I can't figure out what the difference could possibly be.

 

[dmt32]

Don't think msoTristate has msoCFalse just msoFalse which has a value of 0.

msoTrue has value of -1 whilst msoCTrue has value of 1.

Not sure why does not work for you perhaps another here may be able to offer some insight.

dave

 

[vris92]

fwiw I used a workaround to achieve the desired effect. I filtered the data using .IsFiltered = True/False on the series I wished to hide or show. There is some loss of functionality as part of the reason I wanted to be able to hide the data was to more clearly see the trend line (which obviously disappears when the series is filtered). Hiding both is still good enough for now as trendline clarification is secondary to toggling series. Thanks for trying, anyway.

 

[ifog671]

Try this....Its a start and I believe you can tailor to your application.

How to Hide and Unhide Rows/Objects

Sub ItemDetails_Show()

With Sheet2

Range("9:13").EntireRow.Hidden = False (or you can put Range/Shape(“xxx”).visible/hidden=False

.Shapes("ViewDetBtn").Visible = msoFalse

.Shapes("HideDetBtn").Visible = msoCTrue

End With

End Sub

Sub ItemDetails_Hide()

With Sheet2

Range("9:13").EntireRow.Hidden = True (See above)

.Shapes("ViewDetBtn").Visible = msoCTrue

.Shapes("HideDetBtn").Visible = msoFalse

End With

End Sub

 

[Jon Peltier]

Couple things.

boxvalue = Worksheets("Test View").Shapes("Check Box 17").ControlFormat.Value

Yikes! Assign the linked cell of the checkbox, and use the value in the cell.

msoTrue

Just use True and False.

Now to change visibility of a series with lines and markers, without filtering it out of the chart and removing a trendline:

' Hide Series
With MySeries
    .Format.Line.Visible = False
    .MarkerStyle = xlMarkerStyleNone
End With

' Show Series
With MySeries
    .Format.Line.Visible = True
    .MarkerStyle = xlMarkerStyleAutomatic
    ' or xlMarkerStyleCircle, or whatever
End With