vba code to show specified sheets in list box

C

cavasta

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
 
J

JW

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
 
C

cavasta

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top