vba code to show specified sheets in list box
 

I've cloned and adapted a vba code and designed a userform that does the
following:
Displays in a userform list box all the worksheets in an active workbook and
the number of rows in each sheet that contain data. When I double click on a
sheet name in the list box, or highlight it and then click OK, it takes me to
the specified sheet.
Problem: I want the userform list box to display all the sheets in the
workbook except those that I specify in the code. For example, my workbook
contains sheet 1, sheet 2, sheet 3, sheet 4, etc. and I want the userform
list box to display all the sheets except sheet 3. What do I need to insert
and where?
Here's the code:

Option Explicit

Public OriginalSheet As Object
Private Sub CommandButton1_Click()
OriginalSheet.Activate
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim SheetData() As String
Dim ShtCnt As Integer
Dim ShtNum As Integer
Dim Sht As Object
Dim ListPos As Integer

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) = _
Application.CountA(Sht.Range("a3:a65000"))
End Select

ShtNum = ShtNum + 1
Next Sht

With ListBox1
.ColumnWidths = "162 pt;9 pt"
.List = SheetData
.ListIndex = ListPos

End With

End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Call OKButton_Click
End Sub

Private Sub OKButton_Click()
Dim UserSheet As Object
Set UserSheet = Sheets(ListBox1.Value)
If UserSheet.Visible Then
UserSheet.Activate
Else
If UserSheet.Visible = False Then
UserSheet.Visible = True
UserSheet.Activate
End If
End If
UserSheet.Activate
Unload Me
End Sub

Thanks
cavasta

vba code to show specified sheets in list box
 

Didin't have time to look at your whole code, but it looks to me like
it could be done a little cleaner. WIll try to look at it later.
Until then, this will skip over Sheet3
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Sheet3" Then
If sht.Name = ActiveSheet.Name Then _
ListPos = ShtNum - 1
SheetData(ShtNum, 1) = sht.Name
Select Case TypeName(sht)
Case "Worksheet"
SheetData(ShtNum, 2) = _
Application.CountA(sht.Range("a3:a65000"))
End Select
ShtNum = ShtNum + 1
End If
Next sht
cavasta wrote:
I've cloned and adapted a vba code and designed a userform that does the
following:
Displays in a userform list box all the worksheets in an active workbook and
the number of rows in each sheet that contain data. When I double click on a
sheet name in the list box, or highlight it and then click OK, it takes me to
the specified sheet.
Problem: I want the userform list box to display all the sheets in the
workbook except those that I specify in the code. For example, my workbook
contains sheet 1, sheet 2, sheet 3, sheet 4, etc. and I want the userform
list box to display all the sheets except sheet 3. What do I need to insert
and where?
Here's the code:

Option Explicit

Public OriginalSheet As Object
Private Sub CommandButton1_Click()
OriginalSheet.Activate
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim SheetData() As String
Dim ShtCnt As Integer
Dim ShtNum As Integer
Dim Sht As Object
Dim ListPos As Integer

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) = _
Application.CountA(Sht.Range("a3:a65000"))
End Select

ShtNum = ShtNum + 1
Next Sht

With ListBox1
.ColumnWidths = "162 pt;9 pt"
.List = SheetData
.ListIndex = ListPos

End With

End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Call OKButton_Click
End Sub

Private Sub OKButton_Click()
Dim UserSheet As Object
Set UserSheet = Sheets(ListBox1.Value)
If UserSheet.Visible Then
UserSheet.Activate
Else
If UserSheet.Visible = False Then
UserSheet.Visible = True
UserSheet.Activate
End If
End If
UserSheet.Activate
Unload Me
End Sub

Thanks
cavasta

vba code to show specified sheets in list box
 

Thanks JW. I guessed it was a bit untidy but because I'd cloned it and
adapted it for my own purposes I was a bit unsure which bits I could take out
(I've already taken out quite a lot of it).
I've had a quick play around with your suggestion and it looks good. Thanks
for that. How would I go about specifying more than one worksheet that I
wanted excluding? For example, if I wanted to exclude from the list sheet 1
and sheet 3.
Thanks again
cavasta

"JW" wrote:

Didin't have time to look at your whole code, but it looks to me like
it could be done a little cleaner. WIll try to look at it later.
Until then, this will skip over Sheet3
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Sheet3" Then
If sht.Name = ActiveSheet.Name Then _
ListPos = ShtNum - 1
SheetData(ShtNum, 1) = sht.Name
Select Case TypeName(sht)
Case "Worksheet"
SheetData(ShtNum, 2) = _
Application.CountA(sht.Range("a3:a65000"))
End Select
ShtNum = ShtNum + 1
End If
Next sht
cavasta wrote:
I've cloned and adapted a vba code and designed a userform that does the
following:
Displays in a userform list box all the worksheets in an active workbook and
the number of rows in each sheet that contain data. When I double click on a
sheet name in the list box, or highlight it and then click OK, it takes me to
the specified sheet.
Problem: I want the userform list box to display all the sheets in the
workbook except those that I specify in the code. For example, my workbook
contains sheet 1, sheet 2, sheet 3, sheet 4, etc. and I want the userform
list box to display all the sheets except sheet 3. What do I need to insert
and where?
Here's the code:

Option Explicit

Public OriginalSheet As Object
Private Sub CommandButton1_Click()
OriginalSheet.Activate
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim SheetData() As String
Dim ShtCnt As Integer
Dim ShtNum As Integer
Dim Sht As Object
Dim ListPos As Integer

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) = _
Application.CountA(Sht.Range("a3:a65000"))
End Select

ShtNum = ShtNum + 1
Next Sht

With ListBox1
.ColumnWidths = "162 pt;9 pt"
.List = SheetData
.ListIndex = ListPos

End With

End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Call OKButton_Click
End Sub

Private Sub OKButton_Click()
Dim UserSheet As Object
Set UserSheet = Sheets(ListBox1.Value)
If UserSheet.Visible Then
UserSheet.Activate
Else
If UserSheet.Visible = False Then
UserSheet.Visible = True
UserSheet.Activate
End If
End If
UserSheet.Activate
Unload Me
End Sub

Thanks
cavasta