Using a List Box 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. Yo 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.

Excel Macro Print Sheet

Figure 14-16:

CD-ROM

This ListBox makes it easy to select rows in a worksheet.

o * 0 «-

IWUH

HTInr 1

□ Ö

JYitaUK iMW

HIMJtW 4 !

n -

JJVKftr

un« r

*•'W-

..;

n

ÏTCffi»

n -

I.TlilB WJW? H*W>

MUM SlFKTD

iinH

LW MM

Figure 14-16:

CD-ROM

This ListBox makes it easy to select rows in a worksheet.

This example, named ■*■ listbox select rows.xlsm , is available on the companion CD-ROM.

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

The All and None buttons (named selectAllButton and selectNoneButton , respectively) have simple event handler procedures as follows:

Private Sub SelectAllButton_Click() Dim r As Integer

For r = 0 To ListBox1.ListCount - 1

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

Private Sub SelectNoneButton_Click() Dim r As Integer

For r = 0 To ListBox1.ListCount - 1

ListBox1.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 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.UsedRange.Rows(r + 1)

Else

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

End If End If Next r

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

CD-ROM

This example is available on the companion CD-ROM. The file is named % listbox select rows.xlsm

0 -2

Responses

  • JOSE
    How to select a row in worksheet from listbox?
    2 years ago

Post a comment