Microsoft Office Tutorials and References
In Depth Information
ListBox Techniques
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
Private Sub OKButton_Click()
Dim RowRange As Range
Dim RowCnt As Integer,r As Integer
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)
Set RowRange = _
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
This example is available on the companion CD-ROM. The file is named listbox
select rows.xlsm .
Using a ListBox to activate a sheet
The example in this section is just as useful as it is instructive. This example uses a multicolumn
ListBox to display a list of sheets within the active workbook. The columns represent
h The sheet’s name
h The type of sheet (worksheet, chart, or Excel 5/95 dialog sheet)
h The number of nonempty cells in the sheet
h Whether the sheet is visible
Figure 14-17 shows an example of the dialog box.
The code in the UserForm_Initialize procedure (which follows) creates a two-dimensional
array and collects the information by looping through the sheets in the active workbook. It then
transfers this array to the ListBox.
Search JabSto ::

Custom Search