VBA/Excel/Access/Word/Forms/ListBox
Содержание
- 1 Add items to ListBox
- 2 Add item to List Box
- 3 Add names of all open workbooks to the list box
- 4 Assign the data in a worksheet to RowSource of a ListBox
- 5 Determining the selected item
- 6 Evaluating Which Items Are Selected in the Multiselect List Box
- 7 Get all selected items in a list box
- 8 Get selected from ListBox
- 9 Get the selected items in a ListBox
- 10 Make sure the RowSource property is empty
- 11 Select the items programmatically
Add items to ListBox
<source lang="vb">
Sub ShowDialog()
With UserForm1.ListBox1 .MultiSelect = fmMultiSelectSingle .RowSource = "" .AddItem "January" .AddItem "February" .AddItem "March" .AddItem "April" .AddItem "May" .AddItem "June" .AddItem "July" .AddItem "August" .AddItem "September" .AddItem "October" .AddItem "November" .AddItem "December" End With UserForm1.Show
End Sub
</source>
Add item to List Box
<source lang="vb">
Private Sub Form_Load()
Dim obj As AccessObject For Each obj In CurrentData.AllTables Me.yourListBox.AddItem obj.Name Next obj
End Sub
</source>
Add names of all open workbooks to the list box
<source lang="vb">
Sub UserForm_Initialize()
Dim wkBook As Workbook For Each wkBook In Workbooks lstWorkbooks.AddItem wkBook.Name Next
End Sub
</source>
Assign the data in a worksheet to RowSource of a ListBox
<source lang="vb">
Private Sub obMonths_Click()
ListBox1.RowSource = "Sheet1!Months"
End Sub
</source>
Determining the selected item
<source lang="vb"> Private Sub OKButton_Click() Dim Msg As String Msg = "You selected item # " Msg = Msg & ListBox1.ListIndex Msg = Msg & vbNewLine Msg = Msg & ListBox1.Value MsgBox Msg Unload UserForm1 End Sub </source>
Evaluating Which Items Are Selected in the Multiselect List Box
<source lang="vb">
Private Sub cmdRunReports_Click()
Dim varItem As Variant Dim lst As ListBox Set lst = Me.yourList If lst.MultiSelect > 0 Then If lst.ItemsSelected.Count > 0 Then For Each varItem In lst.ItemsSelected DoCmd.OpenReport lst.ItemData(varItem), acViewPreview Next varItem End If End If
End Sub
</source>
Get all selected items in a list box
<source lang="vb">
Private Sub OKButton_Click()
If ListBox1.ListIndex = -1 Then msg = "Nothing" Else msg = "" For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then _ msg = msg & ListBox1.List(i) & vbCrLf Next i End If MsgBox "You selected: " & vbCrLf & msg Unload Me
End Sub
</source>
Get selected from ListBox
<source lang="vb">
Private Sub OKButton_Click()
ActiveCell = ListBox1.Value Unload Me
End Sub
</source>
Get the selected items in a ListBox
<source lang="vb"> Private Sub OKButton_Click() Dim Msg As String Dim i As Integer Msg = "You selected" & vbNewLine For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then Msg = Msg & ListBox1.List(i) & vbNewLine End If Next i MsgBox Msg Unload UserForm1 End Sub </source>
Make sure the RowSource property is empty
<source lang="vb">
Sub ShowDialog1()
With UserForm1 .ListBox1.RowSource = "Sheet1!Months" .obMonths.Value = True End With UserForm1.Show
End Sub
</source>
Select the items programmatically
<source lang="vb">
Private Sub SelectAllButton_Click()
For r = 0 To ListBox1.ListCount - 1 ListBox1.Selected(r) = True Next r
End Sub
</source>