Microsoft Office Tutorials and References
In Depth Information
ListBox Techniques
Using a ListBox to select worksheet rows
The example in this section displays a ListBox that consists of the entire used range of the active
worksheet (see Figure 14-16). The user can select multiple items in the ListBox. Clicking the All
button selects all items, and clicking the None button deselects all items. Clicking OK selects
those corresponding rows in the worksheet. You can, of course, select multiple noncontiguous
rows directly in the worksheet by pressing Ctrl while you click the row borders. However, you
might find that selecting rows is easier when using this method.
This example, named listbox select rows.xlsm , is available on the companion
Selecting multiple items is possible because the ListBox’s MultiSelect property is set to 1 -
fmMultiSelectMulti . The check boxes on each item are displayed because the ListBox’s
ListStyle property is set to 1 - fmListStyleOption .
The UserForm’s Initialize procedure follows. This procedure creates a Range object named
rng that consists of the active sheet’s used range. Additional code sets the ListBox’s
ColumnCount and RowSource properties and adjusts the ColumnWidths property so that
the ListBox columns are proportional to the column widths in the worksheet.
Private Sub UserForm_Initialize()
Dim ColCnt As Integer
Dim rng As Range
Dim cw As String
Dim c As Integer
ColCnt = ActiveSheet.UsedRange.Columns.Count
Set rng = ActiveSheet.UsedRange
With ListBox1
.ColumnCount = ColCnt
.RowSource = rng.Address
cw = “”
For c = 1 To .ColumnCount
cw = cw & rng.Columns(c).Width & “;”
Next c
.ColumnWidths = cw
.ListIndex = 0
End With
End Sub
Search JabSto ::

Custom Search