ex - list box display sheets

  • Thread starter Thread starter sjvenz
  • Start date Start date
S

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
 
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
 
Back
Top