ex - list box display sheets

[sjvenz]

Ok I have got it down to display the sheets that I require, but it stil
displays the others as a check box but with no name

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 shtnum = 1 to ShtCnt
' On first pass shtnum = 1 therefore the if statement i
false and Goto shtnum is executed.
If shtnum > 2 And shtnum < 31 Or shtnum > 39 And shtnum
61 Then

' If currently the activesheet then List Positio
shtnum - 1
If Sheets(shtnum).Name = ActiveSheet.Name Then
ListPos = shtnum - 1
End If

SheetData(shtnum, 1) = Sheets(shtnum).Name

Select Case TypeName(Sheets(shtnum))
Case "Worksheet"
SheetData(shtnum, 2) = "Sheet"
SheetData(shtnum, 3)
Application.CountA(Sheets(shtnum).Cells)
End Select

If Sheets(shtnum).Visible Then
SheetData(shtnum, 4) = "True"
Else
SheetData(shtnum, 4) = "False"
End If
End If
Next Sht

With ListBox1
.ColumnWidths = "100 pt"
.List = SheetData
.ListIndex = ListPos
End With
End Sub


anyone any idea

 

[Patrick Molloy]

What is your question?

The worksheets collection already has all the worksheets
by name. Why are you trying to build your own?

Patrick Molloy
Microsoft Excel MVP