Listbox with Specific sheets

Question

I'm using a ListBox to populate sheets within a workbook for printing using the formula

Private Sub ListBoxSh_Click()

End Sub

Private Sub Worksheet_Activate()

Dim Sh

Me.ListBoxSh.Clear 

For Each Sh In ThisWorkbook.Sheets 

    Me.ListBoxSh.AddItem Sh.Name 

Next Sh 

End Sub

The list clears and re-populates when going to the main sheet of the workbook incase of additional sheets being added.

The problem is, I had hidden sheets with values, formulas and most importantly, itemized pricing that i do no want to be printed (or even show in the listbox, for that matter).

Is this possible? Just assume the sheets are A-H (with potentially more added) but i do no want sheets C and D to print. No additional formula sheets will be added, so basically i just want to exclude C and D permanently

Answer 1

Thank you HansV,

a follow up question: is there a way to do basically the opposite of this action for another department? Basically I also need a Listbox that will populate the hidden sheets and not populate the visible sheets. this will be for accounting so that they only print the pages with material breakdowns and formulas while excluding the input pages (that are visible).

I apologize for my incompetence, I've tried to follow your formula to create my own macro but cant seem to get it to work.

Answer 2

Like this:

Private Sub Worksheet_Activate()
    Dim Sh
    Me.ListBoxSh.Clear
    For Each Sh In ThisWorkbook.Sheets
        If Sh.Visible = xlSheetVisible Then
            Me.ListBoxSh.AddItem Sh.Name
        End If
    Next Sh
End Sub