ex - list box display sheets
 

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

--
Message posted from http://www.ExcelForum.com

ex - list box display sheets
 

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

-----Original Message-----
Ok I have got it down to display the sheets that I
require, but it still
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 is
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 Position
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 ideas


---
Message posted from http://www.ExcelForum.com/

.