Legend manipulation

R

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
437
Good day,

I have a spreadsheet with 17 columns, "A" Column holding date and the various other columns holding data. I have a userform that is a 'display center' where I can pick and choose (through a YES/NO combobox) which columns to graph against time. So I can plot just one of columns, or any number of the columns.

It's just that, if I select just one of the columns, it correctly graphs the one series but the Legend displays all 16 possibilities. What I want is for the legend to show only those columns I wish to graph.


Code: 
Private Sub AssayNi()

Dim ChartData20 As Range
Dim ChartData21 As Range
Dim ChartData22 As Range
Dim ChartData23 As Range
Dim ChartData24 As Range
Dim ChartData25 As Range
Dim ChartData26 As Range
Dim ChartData27 As Range
Dim ChartData28 As Range
Dim ChartData29 As Range
Dim ChartData30 As Range
Dim ChartData31 As Range
Dim ChartData32 As Range
Dim ChartData33 As Range
Dim ChartData34 As Range
Dim ChartData35 As Range
Dim ChartIndex50 As Integer

Dim ChartName20 As String
Dim ChartName21 As String
Dim ChartName22 As String
Dim ChartName23 As String
Dim ChartName24 As String
Dim ChartName25 As String
Dim ChartName26 As String
Dim ChartName27 As String
Dim ChartName28 As String
Dim ChartName29 As String
Dim ChartName30 As String
Dim ChartName31 As String
Dim ChartName32 As String
Dim ChartName33 As String
Dim ChartName34 As String
Dim ChartName35 As String

ChartIndex50 = ComboBox50.ListIndex 'Various Elements
ChartIndex20 = ComboBox20.ListIndex 'Autoclave Feed
ChartIndex21 = ComboBox21.ListIndex 'Flash Discharge 1
ChartIndex22 = ComboBox22.ListIndex 'Flash Discharge 2
ChartIndex23 = ComboBox23.ListIndex 'HD Reactor
ChartIndex24 = ComboBox24.ListIndex 'Polished PLS
ChartIndex25 = ComboBox25.ListIndex 'IR2 Feed
ChartIndex26 = ComboBox26.ListIndex 'Cu/Fe Free
ChartIndex27 = ComboBox27.ListIndex 'Impurity Feed
ChartIndex28 = ComboBox28.ListIndex 'Imp SX Strip
ChartIndex29 = ComboBox29.ListIndex 'SLN Thickener OF
ChartIndex30 = ComboBox30.ListIndex 'Ni EW Feed
ChartIndex31 = ComboBox31.ListIndex 'Ni EW Anolyte
ChartIndex32 = ComboBox32.ListIndex 'WLN Feed
ChartIndex33 = ComboBox33.ListIndex 'PEN Feed
ChartIndex34 = ComboBox34.ListIndex 'PEN1 Thickener
ChartIndex35 = ComboBox35.ListIndex 'PEN2 Clarifier
ChartName20 = "Autoclave Feed"
ChartName21 = "Flash Discharge 1"
ChartName22 = "Flash Discharge 2"
ChartName23 = "HD Reactor"
ChartName24 = "Polished PLS"
ChartName25 = "IR2 Feed"
ChartName26 = "Cu/Fe Free"
ChartName27 = "Impurity Feed"
ChartName28 = "Impurity Strip"
ChartName29 = "SLN Thk O/F"
ChartName30 = "NiEW Feed"
ChartName31 = "NiEW Anolyte"
ChartName32 = "WLN Feed"
ChartName33 = "PEN Feed"
ChartName34 = "PEN1 Thk O/F"
ChartName35 = "PEN2 Clar O/F"

'Setting up DT Picker
Set sh = Sheets("Main Element Profiles")
Set r = sh.Range("a:a").Find(CDate(Me.DTPicker3), sh.[a7], xlValues)    ' first date
rn1 = r.Row                                                             ' where the date is
Set r = sh.Range("a:a").Find(CDate(Me.DTPicker4), sh.[a7], xlValues)    ' second date

Set ChartData20 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData21 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData22 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData23 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData24 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData25 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData26 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData27 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData28 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData29 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData30 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData31 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData32 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData33 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData34 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)
Set ChartData35 = Worksheets("Main Element Profiles").Range("GA" & r.Row & ":GA" & rn1)

Application.ScreenUpdating = False
ActiveSheet.Range("E1").Select
ActiveWindow.Zoom = 120
Set MyChart2 = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart
 

'Nickel

     If ChartIndex20 = 0 Then Set ChartData20 = Worksheets("Main Element 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 Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(1).Format.Line.Weight = 1
End With

     If ChartIndex21 = 0 Then Set ChartData21 = Worksheets("Main Element 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 Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(2).Format.Line.Weight = 1
End With

     If ChartIndex22 = 0 Then Set ChartData22 = Worksheets("Main Element 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 Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(3).Format.Line.Weight = 1
End With

     If ChartIndex23 = 0 Then Set ChartData23 = Worksheets("Main Element 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 Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(4).Format.Line.Weight = 1
End With

     If ChartIndex24 = 0 Then Set ChartData24 = Worksheets("Main Element 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 Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(5).Format.Line.Weight = 1
End With

     If ChartIndex25 = 0 Then Set ChartData25 = Worksheets("Main Element Profiles").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 Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(6).Format.Line.Weight = 1
End With

     If ChartIndex26 = 0 Then Set ChartData26 = Worksheets("Main Element 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 Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(7).Format.Line.Weight = 1
End With

     If ChartIndex27 = 0 Then Set ChartData27 = Worksheets("Main Element Profiles").Range("BN" & r.Row & ":BN" & rn1)  'Impurity Feed
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(8).Name = ChartName27
    .SeriesCollection(8).Values = ChartData27
    .SeriesCollection(8).XValues = Worksheets("Main Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(8).Format.Line.Weight = 1
End With

     If ChartIndex28 = 0 Then Set ChartData28 = Worksheets("Main Element Profiles").Range("DY" & r.Row & ":DY" & rn1)  'IMP SX Strip
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(9).Name = ChartName28
    .SeriesCollection(9).Values = ChartData28
    .SeriesCollection(9).XValues = Worksheets("Main Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(9).Format.Line.Weight = 1
End With

     If ChartIndex29 = 0 Then Set ChartData29 = Worksheets("Main Element Profiles").Range("EH" & r.Row & ":EH" & rn1)  'SLN Thickener O/F
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(10).Name = ChartName29
    .SeriesCollection(10).Values = ChartData29
    .SeriesCollection(10).XValues = Worksheets("Main Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(10).Format.Line.Weight = 1
End With

     If ChartIndex30 = 0 Then Set ChartData30 = Worksheets("Main Element Profiles").Range("BW" & r.Row & ":BW" & rn1)  'Ni EW Feed
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(11).Name = ChartName30
    .SeriesCollection(11).Values = ChartData30
    .SeriesCollection(11).XValues = Worksheets("Main Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(11).Format.Line.Weight = 1
End With
 
     If ChartIndex31 = 0 Then Set ChartData31 = Worksheets("Main Element Profiles").Range("CF" & r.Row & ":CF" & rn1)  'Ni EW Anolyte
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(12).Name = ChartName31
    .SeriesCollection(12).Values = ChartData31
    .SeriesCollection(12).XValues = Worksheets("Main Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(12).Format.Line.Weight = 1
End With

     If ChartIndex32 = 0 Then Set ChartData32 = Worksheets("Main Element Profiles").Range("CO" & r.Row & ":CO" & rn1)  'WLN Feed
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(13).Name = ChartName32
    .SeriesCollection(13).Values = ChartData32
    .SeriesCollection(13).XValues = Worksheets("Main Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(13).Format.Line.Weight = 1
End With
 
     If ChartIndex33 = 0 Then Set ChartData33 = Worksheets("Main Element Profiles").Range("CX" & r.Row & ":CX" & rn1)  'PEN Feed
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(14).Name = ChartName33
    .SeriesCollection(14).Values = ChartData33
    .SeriesCollection(14).XValues = Worksheets("Main Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(14).Format.Line.Weight = 1
End With
 
     If ChartIndex34 = 0 Then Set ChartData34 = Worksheets("Main Element Profiles").Range("DG" & r.Row & ":DG" & rn1)  'PEN 1 Thickener O/F
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(15).Name = ChartName34
    .SeriesCollection(15).Values = ChartData34
    .SeriesCollection(15).XValues = Worksheets("Main Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(15).Format.Line.Weight = 1
End With
 
     If ChartIndex35 = 0 Then Set ChartData35 = Worksheets("Main Element Profiles").Range("DP" & r.Row & ":DP" & rn1)  'PEN 2 Clarifier O/F
     
With MyChart2
    .SeriesCollection.NewSeries
    .SeriesCollection(16).Name = ChartName35
    .SeriesCollection(16).Values = ChartData35
    .SeriesCollection(16).XValues = Worksheets("Main Element Profiles").Range("A" & rn1 & ":A" & r.Row)
    .SeriesCollection(16).Format.Line.Weight = 1
End With
 

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 = "Concentration (g/L)"
    .Legend.Font.Size = 5
End With
 
Dim ImageName2 As String
ImageName2 = Application.DefaultFilePath & Application.PathSeparator & "TempChart.jpeg"
MyChart2.Export Filename:=ImageName2
ActiveSheet.ChartObjects(1).Delete
Me.Image2.Picture = LoadPicture(ImageName2)
'Worksheets("Dashboard").Select
ActiveWindow.Zoom = 85
Application.ScreenUpdating = True
 

End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You must log in or register to reply here.

Similar threads

R
  • Question Question
Passing arguments to a graphing function
8 9 10
Replies
93
Views
8K
RawlinsCross
R
R
  • Question Question
Code telilng excel not to graph sequential identical numbers
Replies
7
Views
766
RawlinsCross
R
R
  • Question Question
Userform Chart, adding, deleting series with combobox entries
Replies
0
Views
517
RawlinsCross
R
D
  • Question Question
after transferring code it works but should i change name
Replies
2
Views
357
dane_m_steyne
D

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

Share this page

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