动态填充列表框 [英] Dynamically populate listboxes

问题描述

我有两列，其中一个具有状态，另一个具有名称。我想做的是通过选项按钮选择staus，并且根据应该在列表框/组合框中选择和填充名称的选项。

  Private Sub CommandButton1_Click（）
 Dim ListOfNames（）As Variant 
 Dim ws As Worksheet 
设置ws =工作表（2）
 Dim Count As Long 
 Dim StatusVal As String 
 Dim j As Long，k As Long，iRow As Long 
 
j = 0 
k = 0 
如果OptionButton1.Value = True然后
 StatusVal =退休
j = j + 1 
 ElseIf OptionButton2.Value = True然后
 StatusVal =Employed
j = j + 1 
 ElseIf OptionButton3.Value = True Then 
 StatusVal =On Leave
j = j + 1 
 Else 
 ListVal =未选择
 End If 
 
'计算excel中的行数
 iRow = ws.Cells（Rows.Count，1）_ 
。 End（xlUp）.Offset（1，0）.Row 
 
 ReDim ListOfNames（iRow，j）
'头的第一行
对于Count = 2 To iRow  -  1 Step 1 
如果StatusVal = ws.Cells（Count，15）.Value Then 
k = k + 1 
 ListOfNames（k，j）= ws.Cells（Count，1）.Value 
结束如果
下一个
与ListBox1 
 .list（）= L istOfAccounts 
 End with 
 End Sub 
  

解决方案

我希望这有帮助！我发现两个问题。

• 在列表框中没有设置要求的属性。我使用AddItem方法添加到列表框中，因为您有一个多维数组。


• 您将ListOfAccounts分配给列表，而不是您声明的ListOfEmployees。

我还添加了一个

  ListBox1.Clear 
  

清除按钮按下之间的内容。

  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 
如果OptionButton1.Value = True然后
 StatusVal =退休
j = j + 1 
 ElseIf OptionButton2.Value = True然后
 StatusVal =雇用
j = j + 1 
 ElseIf OptionButton3.Value = True Then 
 StatusVal =离开
j = j + 1 
 Else 
 ListVal =未选择
结束如果
 
'计数的行在excel 
 iRow = ws.Cells（Rows.Count，1）_ 
 .End（xlUp）.Offset（1，0）.Row 
 ReDim ListOfNames（iRow，j）
'头的第一行
对于Count = 2 To iRow  -  1 Step 1 
如果StatusVal = ws.Cells（Count，15）.Value Then 
k = k + 1 
 ListOfNames（k，j） = ws.Cells（Count，15）.Value 
 End If 
 Next 
 Dim z 
对于z = 0 To k 
 Me.ListBox1.AddItem（ListOfNames（ z，j））
下一个
 End Sub 
  

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
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, 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

这篇关于动态填充列表框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！