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

■ The type of sheet (worksheet, chart, or Excel 5/95 dialog sheet)

■ The number of nonempty cells in the sheet

■ Whether the sheet is visible

Figure 14-17 shows an example of the dialog box.

A

K

.

Vnul

ai

Uu* TJR

■MC** VrJii-

3sr

L Swi 0 "> Ji

*

>J rr' -1 <-. n

H Ir^v

•.wr-i ' rr 7 T m-r j

7 ■

Ehv rfi Wr 0 Paw

U

r

ivniWrt

1

12

17

Ifi li

•> W Ui

wr4 L ^J

■» ■

J

Figure 14-17: This dialog box lets the user activate a sheet.

Figure 14-17: This dialog box lets the user activate a sheet.

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.

Public OriginalSheet As Object

Private Sub UserForm_Initialize() Dim SheetData() As String Set OriginalSheet = ActiveSheet ShtCnt = ActiveWorkbook.Sheets.Count ReDim SheetData(1 To ShtCnt, 1 To 4) ShtNum = 1

For Each Sht In ActiveWorkbook.Sheets

If Sht.Name = ActiveSheet.Name Then _

ListPos = ShtNum - 1 SheetData(ShtNum, 1) = Sht.Name Select Case TypeName(Sht) Case "Worksheet"

SheetData(ShtNum, 2) = "Sheet" SheetData(ShtNum, 3) = _

Application.CountA(Sht.Cells) Case "Chart"

SheetData(ShtNum, 2) = "Chart" SheetData(ShtNum, 3) = "N/A" Case "DialogSheet"

SheetData(ShtNum, 2) = "Dialog" SheetData(ShtNum, 3) = "N/A"

End Select If Sht.Visible Then

SheetData(ShtNum, 4) = "True"

Else

SheetData(ShtNum, 4) = "False" End If

ShtNum = ShtNum + 1 Next Sht With ListBoxl

.ColumnWidths = "100 pt;30 pt;40 pt;50 pt" .List = SheetData .Listlndex = ListPos End With End Sub

The ListBox1_Click procedure follows:

Private Sub ListBox1_Click() If cbPreview Then _

Sheets(ListBox1.Value).Activate

End Sub

The value of the CheckBox control (named cbPreview ) determines whether the selected sheet is previewed when the user clicks an item in the ListBox.

Clicking the OK button (named OKButton ) executes the OKButton_Click procedure, which follows:

Private Sub OKButton_Click() Dim UserSheet As Object Set UserSheet = Sheets(ListBox1.Value) If UserSheet.Visible Then UserSheet.Activate

Else

If MsgBox("Unhide sheet?", _

vbQuestion + vbYesNoCancel) = vbYes Then UserSheet.Visible = True UserSheet.Activate

Else

OriginalSheet.Activate End If End If Unload Me End Sub

The OKButton_Click procedure creates an object variable that represents the selected sheet. If the sheet is visible, it is activated. If it's not visible, the user is presented with a message box asking whether it should be unhidden. If the user responds in the affirmative, the sheet is unhidden and activated. Otherwise, the original sheet (stored in a public object variable named OriginalSheet ) is activated.

Double-clicking an item in the ListBox has the same result as clicking the OK button. The ListBox1_DblClic] procedure, which follows, simply calls the OKButton_Click procedure.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Call OKButton_Click End Sub

CD-ROM

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

0 0

Post a comment