Excluded Sheet Tabs from populating a ListBox?

RWayneH
RWayneH used Ask the Experts™
on
Is there a way to exclude a couple sheet tabs for a ListBox?  The following code works pretty good to populate it, but what if I want to exclude Sheet1 and Sheet2?

Private Sub UserForm_Initialize()
Dim n As Integer
Do  'populate ListBox1
    n = n + 1
    ListBox1.AddItem Sheets(n).Name 'shows all visible and hidden sheet tabs.
Loop Until n = Worksheets.Count
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Maybe something like
Private Sub UserForm_Initialize()
    Dim n                     As Integer
    Do  'populate ListBox1
        n = n + 1
        If Sheet(n).Name <> "Sheet1" And Sheet(n).Name <> "Sheet2" Then
            ListBox1.AddItem Sheets(n).Name    'shows all visible and hidden sheet tabs.
        End If
    Loop Until n = Worksheets.Count
End Sub

Open in new window

Author

Commented:
Thanks looks like that will do it.  The sheets are not Sheet1 and Sheet2, but have different names.  I am guess that instead of And, it would use an Or?  Working with it.

Author

Commented:
Will testing this it did not work, I had to put an "s" and use Sheets, so it would that the line successfully.  I added the Or instead of the And, but that did not work either.  Any ideas?

If Sheets(n).Name <> "Budget Items" Or Sheets(n).Name <> "MasterPrimaryDepositAccount" Or Sheets(n).Name <> "DefaultAccountPg" Then

Open in new window


It still included those sheet tabs.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
It doesn't work because you are using the OR condition. You should replace it with AND and it will work properly.

If Worksheets(n).Name <> "Budget Items" And Worksheets(n).Name <> "MasterPrimaryDepositAccount" And Worksheets(n).Name <> "DefaultAccountPg" Then

Open in new window


Another point to remember is, Sheets collection also include the Chart Sheets. So if you are only concerned with the Data Sheets, you should use Worksheets collection instead of Sheets collection.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
When the number of items to be excluded gets long, it is neater to use a Select Case block to exclude them.  Also, if the user might change the name of the worksheets, I suggest using their CodeName instead of tab name in the Select Case. You would use sh.CodeName instead of sh.Name in the first case--and make sure you get the code names correctly from the Properties pane in the VBA Editor (they aren't enclosed in parentheses).
Private Sub UserForm_Initialize()
Dim sh As Worksheet
For Each sh In Worksheets  'populate ListBox1
    Select Case sh.Name
    Case "Budget Items", "MasterPrimaryDepositAccount", "DefaultAccountPg"  'Ignore these
    Case Else
        ListBox1.AddItem sh.Name 'shows all visible and hidden sheet tabs.
    End Select
Next
End Sub

Open in new window

Author

Commented:
Thanks for help with this
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial