Switch chart points ON/OFF help request

[joecrabtree]

To all,

I have the following macro that turns the points off on an embedded
chart. However I want to assign this to a check box, so when I check
it the points turn on, and when I un-check it the points turn off. I
believe that the macro will need to be modified to turn the points
back on again?


Sub pointson()

ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
End Sub

Any ideas?

Thanks in advance for your help,

Regards

Joseph Crabtree

 

[Jon Peltier]

I made an embedded line chart with series AA and BB, both with default
formatting. I added two Forms menu checkboxes, which I renamed check_AA and
check_BB. I wrote a macro called ChangePoints, and assigned this macro to
both checkboxes. Here is the macro:

Sub ChangePoints()
Dim sSeriesName As String
Dim bSeriesShow As Boolean

sSeriesName = Mid(Application.Caller, InStr(Application.Caller, "_") + 1)
bSeriesShow = (ActiveSheet.CheckBoxes(Application.Caller).Value = 1)

With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(sSeriesName)
If bSeriesShow Then
.Border.LineStyle = xlAutomatic
.MarkerStyle = xlAutomatic
Else
.Border.LineStyle = xlNone
.MarkerStyle = xlNone
End If
End With

End Sub

Notes:
Application.Caller is the name of the object that called the macro, in this
case check_AA or check_BB.
The CheckBox.Value is either 1 for true or -4146 for false.
If you have different (non-default) formatting, then you need more elaborate
code than BlahStyle = xlAutomatic.


- Jon