I am having trouble finding a way that takes the average from a list box and then displaying that average on the user form. I know you are supposed to use an array, but I am very confused at the moment how to array a second column on a list box. Below is an example the numbers in the text box need to be averaged up and then displayed in the circle I have

Private Sub cmdavg_Click()
    Dim avg As Double
    If Me.cmbComboBox.Value = "" Then
        MsgBox "Please Selecet a Player First"
    Else
        Dim MyArray() As Variant
        Dim formatratio As String
        Dim i As Long
        Me.listbox.List(listbox.ListCount - 1, 1) = formatratio
        
        MyArray = Array(formatratio)
        
        lblavg = "Average = " & Average
    End If
End Sub

Private Sub cmdclear_Click()
    If MsgBox("Are yoy sure you want to clear all players " & vbNewLine & "This cannot be undone", vbYesNo   vbCritical, "Clear All Players") = vbYes Then
        listbox.clear
    End If
End Sub

Private Sub cmdexit_Click()
    If MsgBox("Do you really want to QUIT", vbYesNo   vbQuestion, "Quitting") = vbYes Then
        MsgBox "Thank You Goodbye"
        Unload First_PGA
    End If
End Sub

Private Sub cmdplayer_Click()
    Dim ratio As Double
    Dim formatratio As String
    Dim name As String
    
    Me.listbox.ColumnCount = 2

    If cmbComboBox.Value = "" Then
        MsgBox "Please Select a Player"
    Else
        name = Me.cmbComboBox.Column(0)
        Me.listbox.AddItem name
        ratio = Me.cmbComboBox.Column(3)
        formatratio = FormatNumber(ratio, 1)
        Me.listbox.List(listbox.ListCount - 1, 1) = formatratio
    
    End If

End Sub

Private Sub cmdupdate_Click()
    If txtthreshold = "" Then
        MsgBox "Please enter in a number for the threshold"
    End If
End Sub

Private Sub UserForm_Initialize()
    Dim statsrng As Range
    
    Sheets("FedexStandings").Activate
    
    Set statsrng = Range("B2:E266")
    ThisWorkbook.Names.Add name:="players", RefersTo:=statsrng
    
    
    With Worksheets("FedexStandings")
        Range("E2").Formula = "=(D2/C2)"
        Range("E2").AutoFill Range("E2:E266")
        Range("E2:E266").Select
        Selection.NumberFormat = "0.0"
    End With
    
    Me.cmbComboBox.RowSource = "players"
    Me.cmbComboBox.ColumnCount = 4
    Me.cmbComboBox.ColumnHeads = True
    
End Sub

How to use arrays to store numbers from a list box

CodePudding user response：

Get Average From a List Box Column

• This example uses an Active-X list box and label on a worksheet. Surely you'll manage to apply this to your user form case.
• It uses a loop to write the numbers from the 2nd column of the list box to an array and Application.Average to retrieve the average from the array and write it to the label.

Sub ListBoxAverage()
    
    Dim Players() As Variant: Players = VBA.Array("Tim", "Jon", "Sue")
    Dim Numbers() As Variant: Numbers = VBA.Array(1, 2, 4)
    
    Dim rUpper As Long: rUpper = UBound(Players)
    
    Dim Arr() As Double ' Array to Hold the Numbers
    Dim r As Long ' List Box Row Counter
    Dim n As Long ' Array Element Counter
    
    With Sheet1.ListBox1
        ' Populate.
        .Clear
        .ColumnCount = 2
        For r = 0 To rUpper
            .AddItem
            .List(r, 0) = Players(r) ' 1st column
            .List(r, 1) = Numbers(r) ' 2nd column
        Next r
        ' Write the values from the 2nd column to a Double array.
        ReDim Arr(0 To rUpper)
        Dim rValue As Variant
        For r = 0 To rUpper ' .ListCount - 1
            rValue = .List(r, 1) ' 2nd column
            If IsNumeric(rValue) Then ' it's a number
                Arr(n) = CDbl(rValue)
                n = n   1
            'Else ' it's not a number; do nothing
            End If
        Next r
    End With
    
    With Sheet1.Label1
        ' Write the average to the label.
        If n = 0 Then
            .Caption = "No average."
        Else
            If n < r Then
                ReDim Preserve Arr(0 To n - 1)
            End If
            .Caption = Format(Application.Average(Arr), "0.0")
        End If
    End With
    
End Sub

CodePudding user response：

This may help clarify, it adds a Click Event Method to the ListBox ... so click on an entry in the ListBox and the messages tell you what they know about it

Private Sub ListBox1_Click()
   MsgBox Me.ListBox1.Value
   MsgBox Me.ListBox1.ListIndex
   MsgBox Me.ListBox1.List(Me.ListBox1.ListIndex, 0)  ' The Value of Column 1
   MsgBox Me.ListBox1.List(Me.ListBox1.ListIndex, 1)  ' The Value of Column 2
End Sub

Alternatively if you want to know the overall average you just add all column 2 entries and divide by the number of entries there are

Private Sub ListBox1_Click()
   Dim i As Long
   Dim iMax As Long
   Dim SubTot As Double
   
   SubTot = 0
   iMax = UBound(Me.ListBox1.List)
   For i = 0 To iMax
      SubTot = SubTot   Me.ListBox1.List(i, 1)
   Next i
   MsgBox "The Average of ALL people is " & Round((SubTot / (iMax   1)), 2)
End Sub