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.







■MC** VrJii-


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







Ifi li

•> W Ui

wr4 L ^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"


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 _


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


If MsgBox("Unhide sheet?", _

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


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


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

0 0

Post a comment