Legend is not deleting entries when selecting data

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
437
I have a interactive userform where users can select any number of 15 sets of data and then plot them (x axis is time and is common to all data sets).

The data sets are picked by a "yes", "no" combobox before clicking the "Load" command button. How do I get the legend to plot only those selections "yes" rather than all 15 sets of data? Keep in mind that only the "yes" data is the only data being plotted so that works. It's just the legend shows all the series names.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I think the problem is I define all the series so they all show up on the legend. For brevity I'll include the first 7 of the 15 data series

Code:
'this part of the code represents all sets of the data.  "yes" or "no" for each combobox
ChartIndex20 = ComboBox20.ListIndex
ChartIndex21 = ComboBox21.ListIndex
ChartIndex22 = ComboBox22.ListIndex
ChartIndex23 = ComboBox23.ListIndex
ChartIndex24 = ComboBox24.ListIndex
ChartIndex25 = ComboBox25.ListIndex
ChartIndex26 = ComboBox26.ListIndex

Then I name the series

Code:
ChartName20 = "Series 1"
ChartName21 = "Series 2"
ChartName22 = "Series 3"
ChartName23 = "Series 4"
ChartName24 = "Series 5"
ChartName25 = "Series 6"
ChartName26 = "Series 7"

All the chart series are set to NA(). I think this is where I'm going wrong cause all the series are being plotted either with data or with NA(). ColumnGA in the spreadsheet is all NA(). R.Row and rn1 are just variables from DTpicker.

Code:
Set ChartData20 = Worksheets("Main Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData21 = Worksheets("Main Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData22 = Worksheets("Main Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData23 = Worksheets("Main Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData24 = Worksheets("Main Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData25 = Worksheets("Main Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData26 = Worksheets("Main Profiles").Range("GA" & r.Row & ":GA" & rn1)

Then comes the plotting depending on if the index is "yes" or "no":

Code:
If ChartIndex20 = 0 Then Set ChartData20 = Worksheets("Main Profiles").Range("C" & r.Row & ":C" & rn1)  'Autoclave Feed
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = ChartName20
    .SeriesCollection(1).Values = ChartData20
    .SeriesCollection(1).XValues = Worksheets("Main Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(1).Format.Line.Weight = 1
End With

     If ChartIndex21 = 0 Then Set ChartData21 = Worksheets("Main Profiles").Range("L" & r.Row & ":L" & rn1)  'Flash Discharge 1
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(2).Name = ChartName21
    .SeriesCollection(2).Values = ChartData21
    .SeriesCollection(2).XValues = Worksheets("Main Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(2).Format.Line.Weight = 1
End With

     If ChartIndex22 = 0 Then Set ChartData22 = Worksheets("Main Profiles").Range("U" & r.Row & ":U" & rn1)  'Flash Discharge 2
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(3).Name = ChartName22
    .SeriesCollection(3).Values = ChartData22
    .SeriesCollection(3).XValues = Worksheets("Main Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(3).Format.Line.Weight = 1
End With

     If ChartIndex23 = 0 Then Set ChartData23 = Worksheets("Main Profiles").Range("AD" & r.Row & ":AD" & rn1)  'HD Reactor
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(4).Name = ChartName23
    .SeriesCollection(4).Values = ChartData23
    .SeriesCollection(4).XValues = Worksheets("Main Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(4).Format.Line.Weight = 1
End With

     If ChartIndex24 = 0 Then Set ChartData24 = Worksheets("Main Profiles").Range("AM" & r.Row & ":AM" & rn1)  'Polished PLS
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(5).Name = ChartName24
    .SeriesCollection(5).Values = ChartData24
    .SeriesCollection(5).XValues = Worksheets("Main Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(5).Format.Line.Weight = 1
End With

     If ChartIndex25 = 0 Then Set ChartData25 = Worksheets("Main EProfiles").Range("AV" & r.Row & ":AV" & rn1)  'IR2 Feed
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(6).Name = ChartName25
    .SeriesCollection(6).Values = ChartData25
    .SeriesCollection(6).XValues = Worksheets("Main Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(6).Format.Line.Weight = 1
End With

     If ChartIndex26 = 0 Then Set ChartData26 = Worksheets("Main Profiles").Range("BE" & r.Row & ":BE" & rn1)  'Cu/Fe Free
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(7).Name = ChartName26
    .SeriesCollection(7).Values = ChartData26
    .SeriesCollection(7).XValues = Worksheets("Main Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(7).Format.Line.Weight = 1
End With

Finally we do the graph.

Code:
With MyChart2
    .Axes(xlCategory).Select
        Selection.TickLabels.NumberFormat = "m/d/yyyy"
        Selection.TickLabels.NumberFormat = "[$-409]mmm-dd;@"
        Selection.MajorUnitIsAuto = True
    .Axes(xlValue).Select
        Selection.TickLabels.NumberFormat = "#,##0.00"
        Selection.TickLabels.NumberFormat = "#,##0.0,"
    .Axes(xlValue).HasTitle = True
    .Axes(xlValue).AxisTitle.Text = "Y-axis Data"
    .Legend.Font.Size = 5
End With
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top