ListBox with Sheet tabs, except, these and if they are not already visible

RWayneH
RWayneH used Ask the Experts™
on
Hello, I use the follow code to populate a ListBox with sheet tabs, except for a few.  How would I edit, so if a sheet tab is already visible, do not list it in the ListBox?

Private Sub UserForm_Initialize()
    Dim n As Integer
    ActiveWorkbook.Unprotect
    Do  'populate ListBox1
        n = n + 1
        If Worksheets(n).Name <> "Budget Items" And Worksheets(n).Name <> "MasterPrimaryDepositAccount" And Worksheets(n).Name <> "DefaultAccountPg" And Worksheets(n).Name <> "BudgetSummaryPg" Then
            ListBox1.AddItem Sheets(n).Name
        End If
    Loop Until n = Worksheets.Count
    ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Try this.
Private Sub UserForm_Initialize()
    Dim n As Integer
    ActiveWorkbook.Unprotect
    Do  'populate ListBox1
        n = n + 1
        If Worksheets(n).Name <> "Budget Items" And Worksheets(n).Name <> "MasterPrimaryDepositAccount" And Worksheets(n).Name <> "DefaultAccountPg" And Worksheets(n).Name <> "BudgetSummaryPg" Then
            If Sheets(n).Visible <>  xlSheetVisible Then
                ListBox1.AddItem Sheets(n).Name
            End If
        End If
    Loop Until n = Worksheets.Count
    ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

Open in new window

Author

Commented:
Works great, except if all sheet tabs are open.  MsgBox that all sheets are open, instead of a blank ListBox would be nice.  Sorry probably should have mentioned that.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
If you don't want UserForm to be popped up if all the sheets are visible, you may try something like this...

Dim found As Boolean

Private Sub UserForm_Initialize()
    Dim n As Integer
    ActiveWorkbook.Unprotect
    Do  'populate ListBox1
        n = n + 1
        If Worksheets(n).Name <> "Budget Items" And Worksheets(n).Name <> "MasterPrimaryDepositAccount" And Worksheets(n).Name <> "DefaultAccountPg" And Worksheets(n).Name <> "BudgetSummaryPg" Then
            If Sheets(n).Visible <> xlSheetVisible Then
                found = True
                ListBox1.AddItem Sheets(n).Name
            End If
        End If
    Loop Until n = Worksheets.Count
    ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub


Private Sub UserForm_Activate()
    If Not found Then
        Me.Hide
        MsgBox "All Sheets are visible.", vbInformation
        Unload Me
    End If
End Sub

Open in new window

NorieAnalyst Assistant

Commented:
What do you mean by 'open'?

Do you mean visible?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
And in case all the sheets are visible and you want UserForm to be popped up with other controls on it but hide the empty ListBox, you may try this...
Private Sub UserForm_Initialize()
    Dim n As Integer
    Dim found As Boolean
    ActiveWorkbook.Unprotect
    Do  'populate ListBox1
        n = n + 1
        If Worksheets(n).Name <> "Budget Items" And Worksheets(n).Name <> "MasterPrimaryDepositAccount" And Worksheets(n).Name <> "DefaultAccountPg" And Worksheets(n).Name <> "BudgetSummaryPg" Then
            If Sheets(n).Visible <> xlSheetVisible Then
                found = True
                ListBox1.AddItem Sheets(n).Name
            End If
        End If
    Loop Until n = Worksheets.Count
    If Not found Then
        Me.ListBox1.Visible = False
        MsgBox "All Sheets are visible.", vbInformation
    End If
    ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

Open in new window

Author

Commented:
open was a bad word to use, better word would have been visible

Author

Commented:
not getting the no popup to work, however no error? no MsgBox??
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Where did you place those codes? The code should be placed on UserForm Module.

In the attached, click on the button called "Show UserForm" on "Budget Items" Sheet and since no worksheet is hidden, you will get a message and the UserForm will not be popped up.

Now, hide some worksheets and click the button again and this time UserForm will be popped up and ListBox will be showing the hidden worksheets.
UserForm.xlsm

Author

Commented:
Thanks for your 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