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]

The labels that should both sum up numbers based on the range determined by DTPicker 1 and DTPicker 2 on the RoadMap uerfom (that's the first main feature of this project) and then produce a graph when clicked on that gives the daily numbers over time of additional DTPicker 1 and 2 inside the EBAL1 userform (that's the second main feature of this project) are those labels that have the caption "Label 47".

They are all associated or located next to an arrow or line on the RoadMap userform. In fact, they all have the Tag "ChartButton". The other labels (for identification of tanks) do not have any tag.

 

[Jerry Sullivan]

I'm not completely following what clicking each of those two labels does.

I think you are saying that after clicking the Linking label (currently displaying "Label47"), the caption is updated to display a summation of some data, then create and display a chart (based on date range selected).

I'm less clear on what, if anything, happens when the the User label (currently displaying "AC Feed Tanks") is clicked.

 

[RawlinsCross]

Nothing should have when clicking the User label. It is just there simply for demonstrative purposes.

When you click on the Linking Labels it gives you the graph for that link. When you click through the elemental buttons, all the linking labels on the userform Roadmap should update to the summation of that particular element based on the RoadMap DTPickers? Sorry, if I haven't been totally clear. Does that make sense?

 

[Jerry Sullivan]

Okay, I think I'm following you.

For the linking labels, if you make the change to the VBA code I showed in Post #28 , that should work to display the chart. No changes are needed to the properties that you had set for these controls.

Name: LT_ACFEED
Caption: (numeric value that will be updated each time an elemental button is clicked).
Tag: ChartButton

For the action upon clicking the elemental buttons, do you already have a procedure within the RoadMap userform that does the summation ? If so, add a call to that procedure (I'll refer to this procedure as Sub SumByElement().

Private Sub ElementButtonGroup_Click()
 '--store selection in roadmap property
 ElementButtonGroup.Parent.SelectedElement = ElementButtonGroup.Name
 
 '--resets all Element Buttons to default colors, selection to green
 ElementButtonGroup.Parent.ResetElementButtons
 ElementButtonGroup.BackColor = vbGreen
 ElementButtonGroup.Parent.ResetElementButtons
[COLOR="#0000CD"][B] ElementButtonGroup.Parent.SumByElement[/B][/COLOR]

End Sub

The Sub SumByElement should read the current value of the SelectedElement (e.g. "Ni"), which is stored in the Private Variable msSelectedElement

 

[RawlinsCross]

To just revisit the stage, the genesis of this thread was that I had 45 linking labels (didn't call them that back then) and I wanted to code in summations for all of them for each of the elements (11 of them). Then I thought, it'd be cool if I could also chart the daily numbers if I click on label with an element selected. Traditionally I would have done this using 1 userform for each chart and then the numbers got scary -> 45 X13= 585 userforms!!!!

But yes, for flicking around the element buttons and getting 45 summations based on the DTPicker1/2 values I was successful - but ofcourse, as you'll see, the code is not very good. I had to write 11 sub routines (one for each element). I'll include the 'Ni' one here but substitute 'Ni' for 'Cl' or anything else. So yes, I had successful code but I doubt it's applicable now.


Forgive this code

Private Sub Ni_Click()

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
Dim rng8 As Range
Dim rng9 As Range
Dim rng10 As Range
Dim rng11 As Range
Dim rng12 As Range
Dim rng13 As Range
Dim rng14 As Range
Dim rng15 As Range
Dim rng16 As Range
Dim rng17 As Range
Dim rng18 As Range
Dim rng19 As Range
Dim rng20 As Range
Dim rng21 As Range
Dim rng22 As Range
Dim rng23 As Range
Dim rng24 As Range
Dim rng25 As Range
Dim rng26 As Range
Dim rng27 As Range
Dim rng28 As Range
Dim rng29 As Range
Dim rng30 As Range
Dim rng31 As Range
Dim rng32 As Range
Dim rng33 As Range
Dim rng34 As Range
Dim rng35 As Range
Dim rng36 As Range
Dim rng37 As Range
Dim rng38 As Range
Dim rng39 As Range
Dim rng40 As Range
Dim rng41 As Range
Dim rng42 As Range
Dim rng43 As Range
Dim rng44 As Range
Dim rng45 As Range
Dim ColumnLetter1 As String
Dim ColumnLetter2 As String
Dim ColumnLetter3 As String
Dim ColumnLetter4 As String
Dim ColumnLetter5 As String
Dim ColumnLetter6 As String
Dim ColumnLetter7 As String
Dim ColumnLetter8 As String
Dim ColumnLetter9 As String
Dim ColumnLetter10 As String
Dim ColumnLetter11 As String
Dim ColumnLetter12 As String
Dim ColumnLetter13 As String
Dim ColumnLetter14 As String
Dim ColumnLetter15 As String
Dim ColumnLetter16 As String
Dim ColumnLetter17 As String
Dim ColumnLetter18 As String
Dim ColumnLetter19 As String
Dim ColumnLetter20 As String
Dim ColumnLetter21 As String
Dim ColumnLetter22 As String
Dim ColumnLetter23 As String
Dim ColumnLetter24 As String
Dim ColumnLetter25 As String
Dim ColumnLetter26 As String
Dim ColumnLetter27 As String
Dim ColumnLetter28 As String
Dim ColumnLetter29 As String
Dim ColumnLetter30 As String
Dim ColumnLetter31 As String
Dim ColumnLetter32 As String
Dim ColumnLetter33 As String
Dim ColumnLetter34 As String
Dim ColumnLetter35 As String
Dim ColumnLetter36 As String
Dim ColumnLetter37 As String
Dim ColumnLetter38 As String
Dim ColumnLetter39 As String
Dim ColumnLetter40 As String
Dim ColumnLetter41 As String
Dim ColumnLetter42 As String
Dim ColumnLetter43 As String
Dim ColumnLetter44 As String
Dim ColumnLetter45 As String


Me.Ni.BackColor = vbGreen
Me.Co.BackColor = &H8000000F
Me.Cu.BackColor = &H8000000F
Me.Fe.BackColor = &H8000000F
Me.Na.BackColor = &H8000000F
Me.Mg.BackColor = &H8000000F
Me.Mn.BackColor = &H8000000F
Me.Ca.BackColor = &H8000000F
Me.Si.BackColor = &H8000000F
Me.B.BackColor = &H8000000F
Me.Cl.BackColor = &H8000000F



'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


'Finding the column address (e.g. BB3) that the data is for all the linking labels on the main userform

Set rng1 = Worksheets("EBal").Range("A2:CZZ2").Find("ACFEED_AC_Ni", , xlValues, xlWhole)
ColumnLetter1 = Split(Cells(2, rng1.Column).Address, "$")(1)
Set rng2 = Worksheets("EBal").Range("A2:CZZ2").Find("PLS_HD_Ni", , xlValues, xlWhole)
ColumnLetter2 = Split(Cells(2, rng2.Column).Address, "$")(1)
Set rng3 = Worksheets("EBal").Range("A2:CZZ2").Find("ANOLYTE_AC_Ni", , xlValues, xlWhole)
ColumnLetter3 = Split(Cells(2, rng3.Column).Address, "$")(1)
Set rng4 = Worksheets("EBal").Range("A2:CZZ2").Find("LRWT_PEN_Ni", , xlValues, xlWhole)
ColumnLetter4 = Split(Cells(2, rng4.Column).Address, "$")(1)
Set rng5 = Worksheets("EBal").Range("A2:CZZ2").Find("CCD_WLN_Ni", , xlValues, xlWhole)
ColumnLetter5 = Split(Cells(2, rng5.Column).Address, "$")(1)
Set rng6 = Worksheets("EBal").Range("A2:CZZ2").Find("LRT_PLS_Ni", , xlValues, xlWhole)
ColumnLetter6 = Split(Cells(2, rng6.Column).Address, "$")(1)
Set rng7 = Worksheets("EBal").Range("A2:CZZ2").Find("SXWASH_WLN_Ni", , xlValues, xlWhole)
ColumnLetter7 = Split(Cells(2, rng7.Column).Address, "$")(1)
Set rng8 = Worksheets("EBal").Range("A2:CZZ2").Find("ANOLYTE_WLN_Ni", , xlValues, xlWhole)
ColumnLetter8 = Split(Cells(2, rng8.Column).Address, "$")(1)
Set rng9 = Worksheets("EBal").Range("A2:CZZ2").Find("TOTAL_WLN_Ni", , xlValues, xlWhole)
ColumnLetter9 = Split(Cells(2, rng9.Column).Address, "$")(1)
Set rng10 = Worksheets("EBal").Range("A2:CZZ2").Find("IR1BF_WLN_Ni", , xlValues, xlWhole)
ColumnLetter10 = Split(Cells(2, rng10.Column).Address, "$")(1)
Set rng11 = Worksheets("EBal").Range("A2:CZZ2").Find("IR1_POLISHPLS_Ni", , xlValues, xlWhole)
ColumnLetter11 = Split(Cells(2, rng11.Column).Address, "$")(1)
Set rng12 = Worksheets("EBal").Range("A2:CZZ2").Find("POLISHPLS_CUSX_Ni", , xlValues, xlWhole)
ColumnLetter12 = Split(Cells(2, rng12.Column).Address, "$")(1)
Set rng13 = Worksheets("EBal").Range("A2:CZZ2").Find("POLISHPLS_IR1RECYCLE_Ni", , xlValues, xlWhole)
ColumnLetter13 = Split(Cells(2, rng13.Column).Address, "$")(1)
Set rng14 = Worksheets("EBal").Range("A2:CZZ2").Find("HD_IR1_Ni", , xlValues, xlWhole)
ColumnLetter14 = Split(Cells(2, rng14.Column).Address, "$")(1)
Set rng15 = Worksheets("EBal").Range("A2:CZZ2").Find("ANOLYTE_HD_Ni", , xlValues, xlWhole)
ColumnLetter15 = Split(Cells(2, rng15.Column).Address, "$")(1)
Set rng16 = Worksheets("EBal").Range("A2:CZZ2").Find("IR2FEED_IR2THK_Ni", , xlValues, xlWhole)
ColumnLetter16 = Split(Cells(2, rng16.Column).Address, "$")(1)
Set rng17 = Worksheets("EBal").Range("A2:CZZ2").Find("IR2_CDREMOVAL_Ni", , xlValues, xlWhole)
ColumnLetter17 = Split(Cells(2, rng17.Column).Address, "$")(1)
Set rng18 = Worksheets("EBal").Range("A2:CZZ2").Find("CDREMOVAL_IMPSX_Ni", , xlValues, xlWhole)
ColumnLetter18 = Split(Cells(2, rng18.Column).Address, "$")(1)
Set rng19 = Worksheets("EBal").Range("A2:CZZ2").Find("IMPSX_SLN_Ni", , xlValues, xlWhole)
ColumnLetter19 = Split(Cells(2, rng19.Column).Address, "$")(1)
Set rng20 = Worksheets("EBal").Range("A2:CZZ2").Find("IMPSX_COSX_Ni", , xlValues, xlWhole)
ColumnLetter20 = Split(Cells(2, rng20.Column).Address, "$")(1)
Set rng21 = Worksheets("EBal").Range("A2:CZZ2").Find("COSX_NIEW_Ni", , xlValues, xlWhole)
ColumnLetter21 = Split(Cells(2, rng21.Column).Address, "$")(1)
Set rng22 = Worksheets("EBal").Range("A2:CZZ2").Find("NIEW_RECIRC_Ni", , xlValues, xlWhole)
ColumnLetter22 = Split(Cells(2, rng22.Column).Address, "$")(1)
Set rng23 = Worksheets("EBal").Range("A2:CZZ2").Find("NIEW_ANOLYTE_Ni", , xlValues, xlWhole)
ColumnLetter23 = Split(Cells(2, rng23.Column).Address, "$")(1)
Set rng24 = Worksheets("EBal").Range("A2:CZZ2").Find("ANOLYTE_POL_Ni", , xlValues, xlWhole)
ColumnLetter24 = Split(Cells(2, rng24.Column).Address, "$")(1)
Set rng25 = Worksheets("EBal").Range("A2:CZZ2").Find("CUSX_CUEW_Ni", , xlValues, xlWhole)
ColumnLetter25 = Split(Cells(2, rng25.Column).Address, "$")(1)
Set rng26 = Worksheets("EBal").Range("A2:CZZ2").Find("CUEW_CUSX_Ni", , xlValues, xlWhole)
ColumnLetter26 = Split(Cells(2, rng26.Column).Address, "$")(1)
Set rng27 = Worksheets("EBal").Range("A2:CZZ2").Find("IR1THK_IR1_Ni", , xlValues, xlWhole)
ColumnLetter27 = Split(Cells(2, rng27.Column).Address, "$")(1)
Set rng28 = Worksheets("EBal").Range("A2:CZZ2").Find("IR1THK_BF_Ni", , xlValues, xlWhole)
ColumnLetter28 = Split(Cells(2, rng28.Column).Address, "$")(1)
Set rng29 = Worksheets("EBal").Range("A2:CZZ2").Find("CUSX_IR2_Ni", , xlValues, xlWhole)
ColumnLetter29 = Split(Cells(2, rng29.Column).Address, "$")(1)
Set rng30 = Worksheets("EBal").Range("A2:CZZ2").Find("CUFE_SILICA_Ni", , xlValues, xlWhole)
ColumnLetter30 = Split(Cells(2, rng30.Column).Address, "$")(1)
Set rng31 = Worksheets("EBal").Range("A2:CZZ2").Find("COSX_COEW_Ni", , xlValues, xlWhole)
ColumnLetter31 = Split(Cells(2, rng31.Column).Address, "$")(1)
Set rng32 = Worksheets("EBal").Range("A2:CZZ2").Find("ANOLYTE_PUGS_Ni", , xlValues, xlWhole)
ColumnLetter32 = Split(Cells(2, rng32.Column).Address, "$")(1)
Set rng33 = Worksheets("EBal").Range("A2:CZZ2").Find("FLASH_CCD_Ni", , xlValues, xlWhole)
ColumnLetter33 = Split(Cells(2, rng33.Column).Address, "$")(1)
Set rng34 = Worksheets("EBal").Range("A2:CZZ2").Find("RLS_AC_Ni", , xlValues, xlWhole)
ColumnLetter34 = Split(Cells(2, rng34.Column).Address, "$")(1)
Set rng35 = Worksheets("EBal").Range("A2:CZZ2").Find("LRT_RLS_Ni", , xlValues, xlWhole)
ColumnLetter35 = Split(Cells(2, rng35.Column).Address, "$")(1)
Set rng36 = Worksheets("EBal").Range("A2:CZZ2").Find("IR2THK_IR2_Ni", , xlValues, xlWhole)
ColumnLetter36 = Split(Cells(2, rng36.Column).Address, "$")(1)
Set rng37 = Worksheets("EBal").Range("A2:CZZ2").Find("IR2THK_HD_Ni", , xlValues, xlWhole)
ColumnLetter37 = Split(Cells(2, rng37.Column).Address, "$")(1)
Set rng38 = Worksheets("EBal").Range("A2:CZZ2").Find("SILICON_IR2_Ni", , xlValues, xlWhole)
ColumnLetter38 = Split(Cells(2, rng38.Column).Address, "$")(1)
Set rng39 = Worksheets("EBal").Range("A2:CZZ2").Find("LT_ACFEED_Ni", , xlValues, xlWhole)
ColumnLetter39 = Split(Cells(2, rng39.Column).Address, "$")(1)
Set rng40 = Worksheets("EBal").Range("A2:CZZ2").Find("IR1_IR1THK_Ni", , xlValues, xlWhole)
ColumnLetter40 = Split(Cells(2, rng40.Column).Address, "$")(1)
Set rng41 = Worksheets("EBal").Range("A2:CZZ2").Find("BF_IR1_Ni", , xlValues, xlWhole)
ColumnLetter41 = Split(Cells(2, rng41.Column).Address, "$")(1)
Set rng42 = Worksheets("EBal").Range("A2:CZZ2").Find("POLISHPLS_COEW_Ni", , xlValues, xlWhole)
ColumnLetter42 = Split(Cells(2, rng42.Column).Address, "$")(1)
Set rng43 = Worksheets("EBal").Range("A2:CZZ2").Find("NIEW_NIMETAL_Ni", , xlValues, xlWhole)
ColumnLetter43 = Split(Cells(2, rng43.Column).Address, "$")(1)
Set rng44 = Worksheets("EBal").Range("A2:CZZ2").Find("CUEW_CUMETAL_Ni", , xlValues, xlWhole)
ColumnLetter44 = Split(Cells(2, rng44.Column).Address, "$")(1)
Set rng45 = Worksheets("EBal").Range("A2:CZZ2").Find("COEW_COMETAL_Ni", , xlValues, xlWhole)
ColumnLetter45 = Split(Cells(2, rng45.Column).Address, "$")(1)


Me.ACFeed_AC.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter1 & r.Row & ":" & ColumnLetter1 & rn1)), "#,##0")
Me.PLS_HD.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter2 & r.Row & ":" & ColumnLetter2 & rn1)), "#,##0")
Me.ANOLYTE_AC.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter3 & r.Row & ":" & ColumnLetter3 & rn1)), "#,##0")
Me.LRWT_PEN.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter4 & r.Row & ":" & ColumnLetter4 & rn1)), "#,##0")
Me.CCD_WLN.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter5 & r.Row & ":" & ColumnLetter5 & rn1)), "#,##0")
Me.LRT_PLS.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter6 & r.Row & ":" & ColumnLetter6 & rn1)), "#,##0")
Me.SXWASH_WLN.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter7 & r.Row & ":" & ColumnLetter7 & rn1)), "#,##0")
Me.ANOLYTE_WLN.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter8 & r.Row & ":" & ColumnLetter8 & rn1)), "#,##0")
Me.TOTAL_WLN.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter9 & r.Row & ":" & ColumnLetter9 & rn1)) - WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter5 & r.Row & ":" & ColumnLetter5 & rn1)), "#,##0")
Me.IR1BF_WLN.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter10 & r.Row & ":" & ColumnLetter10 & rn1)), "#,##0")
Me.IR1_POLISHPLS.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter11 & r.Row & ":" & ColumnLetter11 & rn1)), "#,##0")
Me.POLISHPLS_CUSX.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter12 & r.Row & ":" & ColumnLetter12 & rn1)), "#,##0")
Me.POLISHPLS_IR1RECYCLE.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter13 & r.Row & ":" & ColumnLetter13 & rn1)), "#,##0")
Me.POLISHPLS_IR1RECYCLE2.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter13 & r.Row & ":" & ColumnLetter13 & rn1)), "#,##0")
Me.HD_IR1.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter14 & r.Row & ":" & ColumnLetter14 & rn1)), "#,##0")
Me.ANOLYTE_HD.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter15 & r.Row & ":" & ColumnLetter15 & rn1)), "#,##0")
Me.ANOLYTE_HD2.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter15 & r.Row & ":" & ColumnLetter15 & rn1)), "#,##0")
Me.IR2FEED_IR2THK.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter16 & r.Row & ":" & ColumnLetter16 & rn1)), "#,##0")
Me.IR2_CDREMOVAL.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter17 & r.Row & ":" & ColumnLetter17 & rn1)), "#,##0")
Me.CDREMOVAL_IMPSX.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter18 & r.Row & ":" & ColumnLetter18 & rn1)), "#,##0")
Me.IMPSX_SLN.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter19 & r.Row & ":" & ColumnLetter19 & rn1)), "#,##0.0")
Me.IMPSX_COSX.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter20 & r.Row & ":" & ColumnLetter20 & rn1)), "#,##0")
Me.COSX_NIEW.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter21 & r.Row & ":" & ColumnLetter21 & rn1)), "#,##0")
Me.NIEW_RECIRC.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter22 & r.Row & ":" & ColumnLetter22 & rn1)), "#,##0")
Me.NIEW_ANOLYTE.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter23 & r.Row & ":" & ColumnLetter23 & rn1)), "#,##0")
Me.ANOLYTE_POL.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter24 & r.Row & ":" & ColumnLetter24 & rn1)), "#,##0")
Me.CUSX_CUEW.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter25 & r.Row & ":" & ColumnLetter25 & rn1)), "#,##0")
Me.CUEW_CUSX.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter26 & r.Row & ":" & ColumnLetter26 & rn1)), "#,##0")
Me.IR1THK_IR1.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter27 & r.Row & ":" & ColumnLetter27 & rn1)), "#,##0")
Me.IR1THK_BF.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter28 & r.Row & ":" & ColumnLetter28 & rn1)), "#,##0")
Me.CUSX_IR2.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter29 & r.Row & ":" & ColumnLetter29 & rn1)), "#,##0")
Me.CUFE_SILICA.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter30 & r.Row & ":" & ColumnLetter30 & rn1)), "#,##0")
Me.COSX_COEW.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter31 & r.Row & ":" & ColumnLetter31 & rn1)), "#,##0")
Me.ANOLYTE_PUGS.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter32 & r.Row & ":" & ColumnLetter32 & rn1)), "#,##0")
Me.ANOLYTE_PUGS2.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter32 & r.Row & ":" & ColumnLetter32 & rn1)), "#,##0")
Me.FLASH_CCD.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter33 & r.Row & ":" & ColumnLetter33 & rn1)), "#,##0")
Me.RLS_AC.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter34 & r.Row & ":" & ColumnLetter34 & rn1)), "#,##0")
Me.LRT_RLS.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter35 & r.Row & ":" & ColumnLetter35 & rn1)), "#,##0")
Me.IR2THK_IR2.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter36 & r.Row & ":" & ColumnLetter36 & rn1)), "#,##0")
Me.IR2THK_HD.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter37 & r.Row & ":" & ColumnLetter37 & rn1)), "#,##0")
Me.IR2THK_HD2.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter37 & r.Row & ":" & ColumnLetter37 & rn1)), "#,##0")
Me.SILICON_IR2.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter38 & r.Row & ":" & ColumnLetter38 & rn1)), "#,##0")
Me.LT_ACFEED.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter39 & r.Row & ":" & ColumnLetter39 & rn1)), "#,##0")
Me.IR1_IR1THK.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter40 & r.Row & ":" & ColumnLetter40 & rn1)), "#,##0")
Me.BF_IR1.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter41 & r.Row & ":" & ColumnLetter41 & rn1)), "#,##0")
Me.POLISHPLS_COEW.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter42 & r.Row & ":" & ColumnLetter42 & rn1)), "#,##0")
Me.NIEW_NIMETAL.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter43 & r.Row & ":" & ColumnLetter43 & rn1)), "#,##0")
Me.CUEW_CUMETAL.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter44 & r.Row & ":" & ColumnLetter44 & rn1)), "#,##0")
Me.COEW_COMETAL.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter45 & r.Row & ":" & ColumnLetter45 & rn1)), "#,##0")

End Sub

 

[Jerry Sullivan]

Do you want to use this as an opportunity to improve your VBA skills?

I can help you to develop simpler code through a series of steps.

 

[RawlinsCross]

Absolutely, that is the idea! I'll want to understand every line which is a bit overwhelming but I'm not too bothered by that. It's just presently unfamiliar.

 

[Jerry Sullivan]

Okay, start by creating an outline of the steps you want the code to do. You'll revise those notes as you develop the procedure, and that will be your documentation so you remind yourself later the purpose of each step, and the approach you took.

Here's a starting point for you to develop further...

Sub SumByElement()
'--the purpose is to step through each point of the flow process, calculate summations at that point and display each sum in the Label control at that point.
'--each summation will be based on the currently selected element and date range selected by the user.

' Step 1: Get the currently selected element

' Step 2:
' Step 3:
' Step 4:
' Step 5:

End Sub

Use as many steps as you need describe what you want to do. Don't write how you are going to do it yet.

 

[RawlinsCross]

Alright, let's get started... by scribing just you typed (sorry).

Sub SubByElement()

'--the purpose is to step through each point of the flow process, calculate summations at that point and display each sum in the Label control at that pont.
'--each summation will based on the currently selected element and date range selected by the user.

'Step 1:  Get the currently selected element
'Step 2:  For each label in the collection of labels follow steps 3 to 7
'Step 3:  Combine the tag property of the label and the element presently selected.  This is known as the look_up_tag
'Step 4:  Take the look_up_tag and search for that tag which will be above the data to be summated.  
'Step 5:  Find the row range that corresponds to DTPicker1 and DTPicker2.
'Step 6:  Sum up the range for the column from Step 4 and the rows from Step 5
'Step 7:  Output the sum in the caption of the label

end sub

How's that?

 

[Jerry Sullivan]

That's good.

Next, let's write some code. My preference is to write code for just small bite at a time, then test it. As the code is developed, this gives a baseline to roll back to if you run into problems. Often , when people are learning VBA, they try to write all the code, then test it. That approach can be frustrating because it's much harder to find the problem code when it doesn't work.

For the first bite, modify the code in the ElementButtonClass to call SubByElement.

Private Sub ElementButtonGroup_Click()
 '--store selection in roadmap property
 ElementButtonGroup.Parent.SelectedElement = ElementButtonGroup.Name
 
 '--resets all Element Buttons to default colors, selection to green
 ElementButtonGroup.Parent.ResetElementButtons
 ElementButtonGroup.BackColor = vbGreen
 ElementButtonGroup.Parent.ResetElementButtons
[COLOR="#0000FF"][B] ElementButtonGroup.Parent.SumByElement[/B][/COLOR]

End Sub

Then add a MsgBox to the Sub you are developing, just to confirm that it will be run whenever an elemental button is clicked.

Sub SubByElement()

'--the purpose is to step through each point of the flow process, calculate summations at that point and display each sum in the Label control at that pont.
'--each summation will based on the currently selected element and date range selected by the user.
[COLOR="#0000FF"]
'--temporary test only
MsgBox "Element button clicked!" [/COLOR]

'Step 1:  Get the currently selected element
'Step 2:  ....

Test the code by clicking button Ni and 1-2 other buttons. Each time you should see the same Msgbox.