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.

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.

x

Sheet Name

Type

Filled Cells

Visible

Sheet 1

Sheet

0

True

Sheet3

Sheet

1

True

5heet4

Sheet

7

False

Sheet5

Sheet

117

False

Macro 1

Sheet

0

True

Dialog 1

Dialog

N/A

True

Sheeté

Sheet

72

d

Sheet7

Sheet

126

I Preview sheet

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

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 ListBox1

.ColumnWidths = "100 pt;30 pt;40 pt;50 pt" .List = SheetData

.ListIndex = ListPos End With End Sub

The ListBoxl_Click procedure follows:

Private Sub ListBoxl_Click() If cbPreview Then _

Sheets(ListBoxl.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(ListBoxl.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 ListBoxl_DblClick procedure, which follows, simply calls the OKButton_Click procedure.

Private Sub ListBoxl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Call OKButton_Click End Sub

0 -1

Post a comment