Using a List Box to select worksheet rows

The example in this section is actually a useful utility. It 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.

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 such that the ListBox columns are proportional to the column widths in the worksheet.

Private Sub UserForm_Initialize()

ColCnt = ActiveSheet.UsedRange.Columns.Count Set rng = ActiveSheet.UsedRange With ListBoxl

.ColumnCount = ColCnt .RowSource = rng.Address cw = ""

For c = 1 To .ColumnCount cw = cw & rng.Columns(c).Width & ";" Next c

.ColumnWidths = cw .Listlndex = 0 End With End Sub

Figure 14-16: This ListBox makes it easy to select rows in a worksheet.

The All and None buttons (named SelectAllButton and SelectNoneButton, respectively) have simple event handler procedures and are as follows:

Private Sub SelectAllButton_Click()

For r = 0 To ListBoxl.ListCount - 1

ListBoxl.Selected(r) = True Next r End Sub

Private Sub SelectNoneButton_Click() For r = 0 To ListBoxl.ListCount - 1 ListBoxl.Selected(r) = False

Next r End Sub

The OKButton_Click procedure follows. This procedure creates a Range object named RowRange that consists of the rows that correspond to the selected items in the ListBox. To determine whether a row was selected, the code examines the Selected property of the ListBox control. Notice that it uses the Union function to add additional ranges to the RowRange object.

Private Sub OKButton_Click() Dim RowRange As Range RowCnt = 0

For r = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(r) Then RowCnt = RowCnt + 1 If RowCnt = 1 Then

Set RowRange = ActiveSheet.Rows(r + 1)


Union(RowRange, ActiveSheet.Rows(r + 1))

End If End If Next r

If Not RowRange Is Nothing Then RowRange.Select Unload Me End Sub

Was this article helpful?

0 0

Post a comment