Passing arguments to a graphing function

[RawlinsCross]

Good day. I have a userform with approximately 50 labels each containing a number which is the latest number in a column of numbers (i.e. I have 50 columns and say 365 rows). I have a code that calls another userform which has a graph when you click on any of the 50 labels (see code below).

So the long of it is - I don't want to make 50 separate userforms for each of the labels. I'd like to have just one userform containing the code (see below again) that accepts arguments for things like axis title, MinimumScale, NumberFormat, .Range - things like that. How is this done?

Private Sub UserForm_Initialize()

Dim MyChart As Chart
Dim ChartData As Range
Dim ChartName As String
Application.ScreenUpdating = False
Worksheets("Dashboard").Range("H4").Value = ActiveWindow.Zoom
ActiveWindow.Zoom = 85
 
Set ChartData = Worksheets("Main Element Profiles").Range("Y7:Y37")
        

ActiveSheet.Range("B2").Select
Set MyChart = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart

With MyChart
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = ChartName
    .SeriesCollection(1).Values = ChartData
    .SeriesCollection(1).XValues = Worksheets("Main Element Profiles").Range("B7:B37")
    .Legend.Select
        Selection.Delete
    .Axes(xlCategory).Select
        Selection.TickLabels.NumberFormat = "m/d/yyyy"
        Selection.TickLabels.NumberFormat = "[$-409]mmm-dd;@"
    .Axes(xlValue).Select
        Selection.TickLabels.NumberFormat = "#,##0.00"
        Selection.TickLabels.NumberFormat = "#,##0.0,"
    .Axes(xlValue).HasTitle = True
    .Axes(xlValue).MinimumScale = 0
    .Axes(xlValue).AxisTitle.Text = "Na Concentration (g/L)"
End With
   

Dim ImageName As String
ImageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.jpeg"
MyChart.Export filename:=ImageName
ActiveSheet.ChartObjects(1).Delete
ActiveWindow.Zoom = Worksheets("Dashboard").Range("H4").Value
Application.ScreenUpdating = True
ASSAY221FLASH2NA.Image1.Picture = LoadPicture(ImageName)
 
End Sub

 

[RawlinsCross]

Getting interesting now. Okay, everything in your pseudo code makes sense except for the "assuming this expressing is used for all 50 labels". In fact the description for each of the 50 labels is unique. I'll include some examples of the first few labels

Label ACFeed_AC = Element & " Tonnage through Autoclave"
Label ANOLYTE_AC = Element & " Tonnage from Anolyte through Autoclave"
Label PLS_HD = Element & " Tonnage of PLS to HD"
+ 47 more labels each with a unique Element & "from "this" to "that""

*idea* not sure what the tag property of a label is (vs. Name property) but could we not pull the graph userform's caption from there? It can't be pulled from the label's caption as that were the data goes.

Everything else looks fine. So Date1 and Date2 will be sent to MakeChart, the Index will denote the location of the data (e.g. ACFEED_AC_CO).

What's next?

 

[Jerry Sullivan]

John,

Using the Tag property of the Label is one way that the Caption1 value could be stored with the controls. Another option would be to include the Caption1, (and possibly the AxisTitle) values in the worksheet's data table. Those could be located in a row above or below the IndexTitle headers.

Either way could work. It might be easier to setup and make changes to the worksheet instead of the Tags- which can be cumbersome to edit.

Please let me know your preference and then post a small screenshot showing a few rows of your dataset that show the relationship between the header(s) and data values.

 

[RawlinsCross]

Is this approximately what you were looking for?

*post edit* - shoot, how do you send screenshots?

 

[RawlinsCross]

Until I figure out the picture thing (URL?, can't browse your computer?), I've entered some rows above the data and listed the variables and the values for the first label on the Main Userform. Is this what you are looking for?

	ACFEED_AC_
Data Identifier	ACFEED_AC_Ni	ACFEED_AC_Co	ACFEED_AC_Cu	ACFEED_AC_Fe	ACFEED_AC_Na	ACFEED_AC_Mg	ACFEED_AC_Mn	ACFEED_AC_Ca	ACFEED_AC_Si	ACFEED_AC_B	ACFEED_AC_Cl
Caption for Graph	Ni Tonnage Through Autoclave	Co Tonnage Through Autoclave	Cu Tonnage Through Autoclave	Fe Tonnage Through Autoclave	Na Tonnage Through Autoclave	Mg Tonnage Through Autoclave	Mn Tonnage Through Autoclave	Ca Tonnage Through Autoclave	Si Tonnage Through Autoclave	B Tonnage Through Autoclave	Cl Tonnage Through Autoclave
Axis Title	Ni Tonnage (t)	Co Tonnage (t)	Cu Tonnage (t)	Fe Tonnage (t)	Na Tonnage (t)	Mg Tonnage (t)	Mn Tonnage (t)	Ca Tonnage (t)	Si Tonnage (t)	B Tonnage (t)	Cl Tonnage (t)
Number Format	#,##0	#,##0	#,##0	#,##0	#,##0	#,##0	#,##0	#,##0	#,##0	#,##0	#,##0
MinimumScale	Auto	Auto	Auto	Auto	Auto	Auto	Auto	Auto	Auto	Auto	Auto
Maximum Scale	Auto	Auto	Auto	Auto	Auto	Auto	Auto	Auto	Auto	Auto	Auto

<tbody>
</tbody><colgroup><col><col span="11"></colgroup>

 

[Jerry Sullivan]

Yes, that’s great. What sheet and range is shown, and what is the range of the data values below it?

 

[RawlinsCross]

The worksheet is called "EBal" and the range start at row 14 and extends to row 379 but I normally graph it using the DTPicker functionality denoted by the code, partially shown below. But generally, the DTPickers have a maximum range of 365 days of data (or thereabouts).

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

Set rng1 = Worksheets("EBal").Range("A2:AZZ2").Find(Index, , xlValues, xlWhole)
ColumnLetter1 = Split(Cells(2, rng1.Column).Address, "$")(1)

Application.ScreenUpdating = False
Worksheets("Dashboard").Range("H4").Value = ActiveWindow.Zoom
ActiveWindow.Zoom = 85
Me.TextBox1.Value = "Auto"
Me.TextBox2.Value = "Auto"
Set ChartData = Worksheets("EBal").Range(ColumnLetter1 & r.Row & ":" & ColumnLetter1 & rn1)
        

ActiveSheet.Range("B2").Select
Set MyChart = ActiveSheet.Shapes.AddChart(xlXYScatter).Chart

With MyChart
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = ChartName
    .SeriesCollection(1).Values = ChartData
    .SeriesCollection(1).XValues = Worksheets("EBal").Range("B" & rn1 & ":B" & r.Row)

 

[Jerry Sullivan]

And what is the sheet and range for the header info shown in your post #14 ?

Is the Data Identifier "ACFEED_AC_Ni" on sheet EBal, in Cell B2?

 

[RawlinsCross]

The table you see in Post #14 is on top of the corresponding data for ACFEED_AC_Ni, ACFEED_AC_Co, ACFEED_AC_Cu, etc (all the way to 11 elements) on sheet "EBal". Of course you know that the identifier (Index in the code in post #16 ) ACFEED_AC_Ni is used for the Y-data for the ACFEED_AC label with the Ni command button selected.

So that would begin for ACFeed_AC_Ni in cell RC2, the Y-data starts in cell RC14 with the X-data (dates) starting in B14. With the another Labels, they go all the way over to column AGI for the latest label (still not finished defining all the labels) for the label CUFE_SILICA_Ni, Co, Cu, etc.

 

[Jerry Sullivan]

Ok I think I got it. One last clarification- you said "X-data (dates) starting in B14", am I correct in understanding the dates are in Column A (where the find of the datePicker value occurs) and the first Y values are in column B?

 

[RawlinsCross]

Good eye, I must say. Yes, column B has dates that having a time component (depends on when the data is loaded into the spreadsheet). Of course, having a time component confuses DTPicker which is simply a date (perhaps 00:00 time if anything) so in column A14 down to A379 I apply the formula: =DATE(YEAR(B14),MONTH(B14),DAY(B14))

So the A column is simply the dates in the B column without the time component. At this point, I can get the r.Row and rn1 numbers outlined in the code in Post #16 using the data in the A column. But the actual data graphed for the x-axis is the B column.


As an aside, since I've inserted all the lines as one sees to fit in those new variables, perhaps I'll have to change the code in Post #16 from

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

to:

'Setting up DT Picker
Set sh = Sheets("EBal")
Set r = sh.Range("a:a").Find(CDate(Me.DTPicker1), sh.[a14], xlValues)    ' first date
rn1 = r.Row                                                             ' where the date is
Set r = sh.Range("a:a").Find(CDate(Me.DTPicker2), sh.[a14], xlValues)    ' second date