Dynamically populate listboxes

I have two columns where in one has status and other has name. What i want to do is select the staus through a optionbutton and depending on the option the names should be selected and populated in a listbox / combobox.

Private Sub CommandButton1_Click()
Dim ListOfNames() As Variant
Dim ws As Worksheet
Set ws = Worksheets(2)
Dim Count As Long
Dim StatusVal As String
Dim j As Long, k As Long, iRow As Long

j = 0
k = 0
If OptionButton1.Value = True Then
StatusVal = "Retired"
j = j + 1
ElseIf OptionButton2.Value = True Then
StatusVal = "Employed"
j = j + 1
ElseIf OptionButton3.Value = True Then
Stat开发者_运维技巧usVal = "On Leave"
j = j + 1
Else
ListVal = "Not Selected"
End If

'Count the number of rows in excel
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

ReDim ListOfNames(iRow, j)
' first row for header
For Count = 2 To iRow - 1 Step 1
    If StatusVal = ws.Cells(Count, 15).Value Then
    k = k + 1
    ListOfNames(k, j) = ws.Cells(Count, 1).Value
    End If
Next
With ListBox1
  .list() = ListOfAccounts
End With
End Sub

I hope this helps! I spotted two issues.

• There were required properties on the assignment to the list box that were not being set. I used an AddItem method to add to the list box since you had a multidimensional array.
• You were assigning ListOfAccounts to the list instead of the ListOfEmployees you declared.

I also added a

ListBox1.Clear

To clear the contents between button pushes.

Private Sub CommandButton1_Click()

Dim ListOfNames() As Variant

Dim Count As Long
Dim StatusVal As String
Dim j As Long, k As Long, iRow As Long
ListBox1.Clear
j = 0
k = 0
If OptionButton1.Value = True Then
    StatusVal = "Retired"
    j = j + 1
ElseIf OptionButton2.Value = True Then
    StatusVal = "Employed"
    j = j + 1
ElseIf OptionButton3.Value = True Then
    StatusVal = "On Leave"
    j = j + 1
Else
    ListVal = "Not Selected"
End If

'Count the number of rows in excel
iRow = ws.Cells(Rows.Count, 1) _
      .End(xlUp).Offset(1, 0).Row
ReDim ListOfNames(iRow, j)
' first row for header
For Count = 2 To iRow - 1 Step 1
    If StatusVal = ws.Cells(Count, 15).Value Then
        k = k + 1
        ListOfNames(k, j) = ws.Cells(Count, 15).Value
    End If
Next
Dim z
For z = 0 To k
    Me.ListBox1.AddItem (ListOfNames(z, j))
Next
End Sub