asked on

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

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

Norie

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

RWayneH

ASKER

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)

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

Norie

What do you mean by 'open'?

Do you mean visible?

Subodh Tiwari (Neeraj)

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

RWayneH

ASKER

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

RWayneH

ASKER

not getting the no popup to work, however no error? no MsgBox??

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.