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

 

[Jerry Sullivan]

John,

Here is some code for you to try. There's a few setup steps required to get your workbook to sync with the code.

Ultimately, you will end up with a mix of code you already had, plus this code below. Instead of trying to integrate the parts, I'd suggest you start with a copy of your workbook and replace any existing code you have with the suggested code. Once you have that functioning, you can add back in any pieces from your previous code that aren't covered here.

Worksheet EBal:
1. Create a Range named "rngHeaders" that refers to all the header cells starting with the Data Identifiers (e.g. A2:ZZ13)

2. Create a Range named "rngData" that refers to all YValues starting with the first column of Dates. (e.g. A14:ZZ397)

3. Paste this code into a Standard Code Module (like Module1). This is the code that is called to launch the RoadMap userform and get data from the worksheet.

Public Sub LaunchRoadMap()
 '--creates instance of roadmap userform, passes variables to it,
 '     then shows modeless
 
 '--declare variable frm to be a RoadMap userform object
 Dim frm As RoadMap
 
 '--create a new instance of RoadMap
 Set frm = New RoadMap
 
 '--pass worksheet with headers and data to public property of frm
 Set frm.Worksheet = ThisWorkbook.Sheets("EBal")
 
 '--show frm to allow user to interact
 frm.Show vbModeless
End Sub

Public Function lGetHeaderColNumber(wks As Worksheet, _
   sDataIdentifier As String) As Long
   
 '--finds match for specified data identifier in first row of range
 '    named "rngHeaders" on the worksheet.
 '--returns 0 if no match found
 
 Dim rHeaders As Range
 Dim vColNdx As Variant, vRowNdx As Variant
 
 Set rHeaders = wks.Range("rngHeaders")

 vColNdx = Application.Match(sDataIdentifier, rHeaders.Resize(1), 0)
      
 If IsNumeric(vColNdx) Then
   lGetHeaderColNumber = vColNdx
 Else
   lGetHeaderColNumber = 0
 End If
      
End Function

Public Function sGetChartParameter(wks As Worksheet, sDataIdentifier As String, _
   sParameter As String) As String
   
 '--looks up and returns value from within range named "rngHeaders"
 '-- on the worksheet.
 '--finds match for specified parameter in first column of range
 '--finds match for specified data identifier in first row of range
   
 Dim lColNdx As Long
 Dim rHeaders As Range
 Dim vRowNdx As Variant
 
 Set rHeaders = wks.Range("rngHeaders")

 lColNdx = lGetHeaderColNumber(wks:=wks, sDataIdentifier:=sDataIdentifier)
 If lColNdx = 0 Then
   MsgBox "Identifier not found: " & sDataIdentifier
   Exit Function
 End If
 
 vRowNdx = Application.Match(sParameter, rHeaders.Resize(, 1), 0)
 
 If IsNumeric(vRowNdx) Then
   sGetChartParameter = Application.Index(rHeaders, CLng(vRowNdx), lColNdx)
 Else
   MsgBox "Parameter not found: " & sParameter
 End If
 
End Function

Public Function rGetXValuesRange(wks As Worksheet, dtStart As Date, _
   dtEnd As Date) As Range
   
 '--finds the range of dates between specified start and end dates (inclusive)
 '--returns range within the first column of range named "rngData" on the worksheet.
   
 Dim rDates As Range
 Dim vRowNdxStart As Variant, vRowNdxEnd As Variant
 
 Set rDates = wks.Range("rngData").Resize(, 1)

 vRowNdxStart = Application.Match(CLng(dtStart), rDates, 0)
 vRowNdxEnd = Application.Match(CLng(dtEnd), rDates, 0)
 
 If IsNumeric(vRowNdxStart) And IsNumeric(vRowNdxEnd) Then
   Set rGetXValuesRange = Range( _
      Application.Index(rDates, CLng(vRowNdxStart)), _
      Application.Index(rDates, CLng(vRowNdxEnd)))
 Else
   Set rGetXValuesRange = Nothing
 End If
 
End Function

4. Paste this code into Userform A1_EBAL1

Option Explicit

'--userform module variables
Private msAxisTitle As String
Private msCaptionForGraph As String
Private msDataIdentifier As String
Private mrXValues As Range
Private mrYValues As Range

'--public properties
Public Property Let AxisTitle(sAxisTitle As String)
   msAxisTitle = sAxisTitle
End Property

Public Property Let DataIdentifier(sDataIdentifier As String)
   msDataIdentifier = sDataIdentifier
End Property

Public Property Let CaptionForGraph(sCaptionForGraph As String)
   msCaptionForGraph = sCaptionForGraph
End Property

Public Property Set XValues(rXValues As Range)
   Set mrXValues = rXValues
End Property

Public Property Set YValues(rYValues As Range)
   Set mrYValues = rYValues
End Property

'--userform events
Private Sub UserForm_Activate()
 Call MakeChart
End Sub

'--private procedures
Private Sub MakeChart()
 '--creates a new chart, makes a jpg image and displays the image
 Dim MyChart As Chart
 Dim dblZoomSave As Double
 Dim ChartName As String
 Dim ImageName As String
 Dim wksTemp As Worksheet

 Application.ScreenUpdating = False
 
 '--store current zoom
 dblZoomSave = ActiveWindow.Zoom
 ActiveWindow.Zoom = 85
 
 Set wksTemp = ThisWorkbook.Worksheets.Add
 
 wksTemp.Range("A1").Select
 Set MyChart = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart

 With MyChart
   .SeriesCollection.NewSeries
   .SeriesCollection(1).Name = msCaptionForGraph
   .SeriesCollection(1).Values = mrYValues
   .SeriesCollection(1).XValues = mrXValues

   .Legend.Delete
   
   With .Axes(xlCategory)
      .TickLabels.NumberFormat = "m/d/yyyy"
   End With
   
   With .Axes(xlValue)
      .TickLabels.NumberFormat = "#,##0"
      .HasTitle = True
      .MinimumScale = 0
      .AxisTitle.Text = msAxisTitle
   End With

 End With

 ImageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.jpeg"
 MyChart.Export Filename:=ImageName
 
 Application.DisplayAlerts = False
 wksTemp.Delete
 Application.DisplayAlerts = True
 
 '--reset zoom
 ActiveWindow.Zoom = dblZoomSave
 Application.ScreenUpdating = True
 
 Me.Image1.Picture = LoadPicture(ImageName)
 Me.Caption = msCaptionForGraph
End Sub

5. Paste this code into Userform RoadMap (rename your existing form "RoadMap" if it isn't already so named)

Option Explicit

Private msSelectedElement As String
Private msSelectedLabel As String
Private mwksWorksheet As Worksheet
Private ChartButtons() As New ChartButtonClass
Private ElementButtons() As New ElementButtonClass

'--public properties
Public Property Let SelectedElement(sSelectedElement As String)
 msSelectedElement = sSelectedElement
End Property

Public Property Let SelectedLabel(sSelectedLabel As String)
 msSelectedLabel = sSelectedLabel
End Property

Public Property Set Worksheet(wks As Worksheet)
 Set mwksWorksheet = wks
End Property


'--event procedures
Private Sub UserForm_Initialize()

 Call InitializeControls
 
 Call PopulateControlArrays
End Sub

'--private procedures
Private Sub InitializeControls()
 '--set control properties
 
 '--date picker range
 Me.DTPicker1.MinDate = Date - 365
 Me.DTPicker1.MaxDate = Date
 Me.DTPicker2.MinDate = Date - 365
 Me.DTPicker2.MaxDate = Date
 
 '--resets all Element Buttons to default colors
 Call ResetElementButtons
End Sub

  
Private Sub PopulateControlArrays()
 '--assigns each label used to create charts to an array of ChartButtons
 '--each label needs to have its Tag property set to "ChartButton"
 
 '--assigns each commandbutton used to select element an array of ElementButtons
 '--each label needs to have its Tag property set to "ElementButton"
 
 Dim Ctrl As Control
 Dim lChartButtonCount As Long, lElementButtonCount As Long

 For Each Ctrl In Me.Controls
   '--add to array of chartbuttons to use its events
   If TypeName(Ctrl) = "Label" Then
      If Ctrl.Tag = "ChartButton" Then
         lChartButtonCount = lChartButtonCount + 1
         ReDim Preserve ChartButtons(1 To lChartButtonCount)
         Set ChartButtons(lChartButtonCount).ChartButtonGroup = Ctrl
         '--link to this instance of RoadMap
         Set ChartButtons(lChartButtonCount).Parent = Me
      End If
   End If
   '--add to array of elementbuttons to use its events
   If TypeName(Ctrl) = "CommandButton" Then
      If Ctrl.Tag = "ElementButton" Then
         lElementButtonCount = lElementButtonCount + 1
         ReDim Preserve ElementButtons(1 To lElementButtonCount)
         Set ElementButtons(lElementButtonCount).ElementButtonGroup = Ctrl
         '--link to this instance of RoadMap
         Set ElementButtons(lElementButtonCount).Parent = Me
      End If
   End If

 Next Ctrl
End Sub

'--public procedures
Public Sub MakeChart()
 '--calls 2nd user form to make new chart based on parameters_
 '    retrieved from the worksheet using "get" functions.
 
 Dim frm As A1_EBAL1
 Dim rData As Range, rXValues As Range, rYValues As Range
 Dim sAxisTitle As String, sDataIdentifier As String
 Dim sCaptionForGraph As String
 Dim lColNdx As Long
 
 '--build identifier from user selections e.g. "ACFEED_AC_Co"
 If Len(msSelectedLabel) * Len(msSelectedElement) > 0 Then
   sDataIdentifier = msSelectedLabel & "_" & msSelectedElement
 Else
   MsgBox "Must select both Element and Chart Label"
   Exit Sub
 End If

 sAxisTitle = sGetChartParameter(wks:=mwksWorksheet, _
   sDataIdentifier:=sDataIdentifier, _
   sParameter:="Axis Title")
    
 sCaptionForGraph = sGetChartParameter(wks:=mwksWorksheet, _
   sDataIdentifier:=sDataIdentifier, _
   sParameter:="Caption For Graph")
    
 Set rXValues = rGetXValuesRange(wks:=mwksWorksheet, _
   dtStart:=Me.DTPicker1.Value, dtEnd:=Me.DTPicker2.Value)

 '--get column index of matching header relative to rngHeader in sheet
 lColNdx = lGetHeaderColNumber(wks:=mwksWorksheet, _
   sDataIdentifier:=sDataIdentifier)
 
 Set rYValues = rXValues.Offset(0, lColNdx - 1)
 
 '--create a new instance of charting userform A1_EBAL1
 Set frm = New A1_EBAL1
 
 '--pass variables to charting userform
 frm.AxisTitle = sAxisTitle
 frm.CaptionForGraph = sCaptionForGraph
 frm.DataIdentifier = sDataIdentifier
 Set frm.XValues = rXValues
 Set frm.YValues = rYValues
 
 '--show frm
 frm.Show vbModeless

End Sub

Public Sub ResetElementButtons()
 '--resets all Element Buttons to default colors
 Dim Ctrl As Control
 
 Const lDefaultColor As Long = &H8000000F
 
 For Each Ctrl In Me.Controls
   If Ctrl.Tag = "ElementButton" Then
      Ctrl.BackColor = lDefaultColor
   End If
 Next Ctrl
End Sub

6. In RoadMap, set the Tag Property of all 50 labels to "ChartButton". You can select all the labels and change the property of the group instead of changing them separately.

7. In RoadMap, set the Tag Property of all 11 Element command buttons to "ElementButton".

8. Create a Class Module named "ChartButtonClass". This class allows you to control the behavior of all 50 labels.

Option Explicit
 
Public WithEvents ChartButtonGroup As MSForms.Label
 
Private mfrmParent As RoadMap

'--public properties
Public Property Set Parent(frmParent As RoadMap)
 Set mfrmParent = frmParent
End Property


'--event procedures
Private Sub ChartButtonGroup_Click()
 '--store selection in roadmap property
 ChartButtonGroup.Parent.SelectedLabel = ChartButtonGroup.Caption
 
  '--trigger making of new chart
 Call ChartButtonGroup.Parent.MakeChart
End Sub

9. Create a Class Module named "ElementButtonClass". This class allows you to control the behavior of all 11 command buttons.

Option Explicit
 
Public WithEvents ElementButtonGroup As CommandButton
 
Private mfrmParent As RoadMap

'--public properties
Public Property Set Parent(frmParent As RoadMap)
 Set mfrmParent = frmParent
End Property

'--event procedures
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

End Sub

That's a lot of moving parts, so I'd be surprised if it works for you the first time without some trouble shooting. Just let me know if have any problems.

 

[RawlinsCross]

...*crickets chirping*... yikes, that some code you've got there. Looking forward to starting with it and perhaps even understanding what's going on? I'll be back!

 

[Jerry Sullivan]

Hi John,

Sorry if that code looks more daunting than you expected. Just let me know if I can help in any way.

 

[RawlinsCross]

Thanks Jerry. I've worked in the interim as the data columns were not all completed. But I think I'll be able to look at the code this weekend. REALLY looking forward to the challenge. This is my very first 'class module' and I certainly appreciate the help.

 

[RawlinsCross]

Okay, some partial success. As you mentioned, lots of moving parts. But on the positive side, the userform shows up. But two immediate issues:

#1
When I click through the elements, nothing happens. All 50 nodes stay the same "Label 47" (i.e. no summation of numbers DTPicker1.value and DTPicker2.value are both "6/27/2017"). Changing date does not anything.

#2
When I click on a node (for example one I named ACFEED_AC) I get the following prompt:

Identifier not found: Label47_Ni
or
Identifier not found: Label47_Co
(or any of the 11 elements as the suffix)


John

John

 

[RawlinsCross]

It seems that it's getting Label47 from the caption property of the label (which is Label47)?

 

[Jerry Sullivan]

Hi John,

I've been offline for a couple of weeks while traveling.

It will expedite things if I can review a copy of your workbook. Please either upload a copy to a hosting site like Box.com and post a link, or send me a PM and I'll provide you an email address to send the file.

Either way, I'll post a response to you on this thread.

 

[Jerry Sullivan]

Hi John,

Thanks for sending an example file for me to review. The problem is with the properties of the Label controls.

Currently, at each point in the process, you have two Labels that I'll refer to as a User label and a Mapping label

As an example, one pair of these two controls have these key properties:
User label
Name: Label133
Caption: AC Feed Tanks
Tag: (blank)

Linking label
Name: LT_ACFEED
Caption: Label47
Tag: ChartButton

The code I suggested was based on having only one label at each point. The caption of the selected label would be joined with the element caption (e.g. Ni) to make a lookup key LT_ACFEED_Ni. The Tag, ChartButton, was used to identify the Label control as a member of the ChartButtonClass.

Single label (original code)
Caption: LT_ACFEED
Tag: ChartButton

The pair of labels probably arose from your desire to have more user-friendly descriptions instead of captions like: LT_ACFEED.

To do that, I'd suggest you delete all the Linking labels, and modify your existing User labels to follow this pattern.
Single label (modified)
Name: LT_ACFEED
Caption: AC Feed Tanks
Tag: ChartButton

Then modify this procedure in the ChartButtonClass to read the Name property instead of the Caption..

Private Sub ChartButtonGroup_Click()
 '--store selection in roadmap property
 ChartButtonGroup.Parent.SelectedLabel = ChartButtonGroup.Name
 
  '--trigger making of new chart
 Call ChartButtonGroup.Parent.MakeChart
End Sub

Please let me know how that works for you.

 

[RawlinsCross]

My only issue with your suggestion is that the "User Label" identifies the tanks and the "Linking Labels" totalize the mass of element based on DT Picker1 and DT Picker 2 depending on the element button chosen. So normally the "Linking Labels" would show numbers (i.e. all identification is lost). That is the main feature. The graphing feature is a second (equally important) feature where if you click on any "Linking Label" it will graph a daily number vs time again depending on the element button chosen.

So I don't want to lose the identification of the tanks. So instead, will this work? - Instead of deleting the "User Labels", I replace them with "User Textboxes" instead? Would the fact that I'm using labels in one case and textboxes in the other case differentiate the situation enough to make the code work?

John

 

[Jerry Sullivan]

John,

Okay- I see now why you have two labels. They can both be Label controls. I'd only use a TextBox if there's a need for the users to be able to change the values. The Tag property will differentiate the ones that trigger the chart creation.

Which label do you want the user to click on to generate the chart?