Code help Printing multiple worksheets based on criteria

[Javi]

Hi All,

The main objective is to print multiple worksheets from a list on worksheet “Main” Col “C” that has an indicator of true appears in Col “AS”. The user does not necessarily need to see the full list of worksheets only items that are marked in Col “AS” TRUE. I spent much time trying to do this in a user form NO LUCK. I am open for different directions.

Below is my best attempt so far.

I have created an activeX control listbox (On Worksheet "Print_Select") that pulls all of the sheet names into a listbox and allows me to select which sheets I would like to print (see code below works well i.e. multiple sheets etc). I expect have about 100 sheets. On the worksheet "MAIN" each Row represents a worksheet. The name for each worksheet is created on the WS "MAIN" on its specific row, then copied to a cell on each worksheet with code to rename the worksheet. The main list also contains the indicator whether or not the worksheet should be printed. (Problem only sheet names to select from, I do not get my indicator whether to print or not)

I am looking for help/advice on the code to pull in data from worksheet “Main” Column AS that is associated with each worksheet. The "AS" column contains true, false indicating whether or not to print (Starting "AS5"). The sheet names are located on worksheet “MAIN” starting column "C5" (This is identical to all of the specific sheet names).


Thank you in advance for any assistance.

Sub Print_Sh1()

Dim i As Long, c As Long
Dim SheetArray() As String

With ActiveSheet.ListBoxSh
  For i = 0 To .ListCount - 1
      If .Selected(i) Then

        ReDim Preserve SheetArray(c)
          SheetArray(c) = .List(i)
               c = c + 1

End If
Next i

End With

'Sheets(SheetArray()).PrintPreview
If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
Sheets(SheetArray()).PrintOut

End If

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

 

[DanteAmor]

Hope understand what you need. Replace your Worksheet_Activate event with the following:

Private Sub Worksheet_Activate()
  Dim i As Long
  With Me.ListBoxSh
    .Clear
    For i = 5 To Range("C" & Rows.Count).End(3).Row
      If Range("AS" & i).Value = True Then
        .AddItem Range("C" & i)
      End If
    Next
  End With
End Sub

SHEET EVENT
Right click the tab of the "Main" sheet, select view code and paste the code into the window that opens up.

To fill the listbox. Select any other sheet. Now select the sheet "Main"

 

[Javi]

Thank you for your reply. I believe this will work however, I'm not getting an error it is just not populating anything into the list box.

In your code do we need to identify what sheet we are looking to pull the data from "MAIN"

Data is on "Main"
The ListBoxSh is on worksheet "Print_Select"

Thanks again for helping out.

 

[DanteAmor]

Data is on "Main"
The ListBoxSh is on worksheet "Print_Select"

Try this

Private Sub Worksheet_Activate()
  Dim i As Long
  Dim sh as worksheet
  set sh = sheets("Main")
  With Me.ListBoxSh
    .Clear
    For i = 5 To sh.Range("C" & Rows.Count).End(3).Row
      If sh.Range("AS" & i).Value = True Then
        .AddItem sh.Range("C" & i)
      End If
    Next
  End With
End Sub

SHEET EVENT
Right click the tab of the "Print_Select" sheet, select view code and paste the code into the window that opens up.

To fill the listbox. Select any other sheet. Now select the sheet "Print_Select"

 

[Javi]

Thank you absolutely perfect!!!

Below is the second part of the printing code that also works however, if you could kindly help me with some error handling.

The code fails if nothing is selected in the list box, I would like to get a error message that says "Select Sheets To Print" then ends the code.

Also, it would be nice if the default was for all of the worksheets to be highlighted in the list box rather than having to manually do that.


Again thank the worksheet_activate code is exactly what I needed.

Sub Print_Sh1()
Dim i As Long, c As Long
Dim SheetArray() As String

With ActiveSheet.ListBoxSh
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
                              
                End If
               
       
        Next i
       
    End With

'Sheets(SheetArray()).PrintPreview
If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
'Application.Dialogs(xlDialogPrinterSetup).Show
Sheets(SheetArray()).PrintOut

End If


End Sub

 

[Javi]

I found this old post with the below code (Credit to Norie), just don't know how to incorporated into the above code.

MsgBox if nothing selected in Listbox?

Hi all, I am trying to create a MsgBox that alerts a user when nothing was selected in ListBox5. It is a multiselect listbox. What do I need to tweak here? If ListBox5.ListCount = -1 Then MsgBox "Missing query selection." & vbNewLine & vbNewLine & "Please make a RETAILER...

www.mrexcel.com

Re: MsgBox if nothing selected in Listbox? CODE PROVIDED

ListIndex doesn't really work with multiselect listboxes and ListCount will tell you the no of items in the listbox.

To check if nothing has been selected in a multiselect listbox you need to loop through the list.
Code:

Dim boolSelected As Boolean

With ListBox1
For I = 0 To. ListCount - 1
boolSelected = boolSelected And. Selected(I)
Next I
End With

If Not boolSelected Then
MsgBox "Nothing selected in listbox."
End If

 

[DanteAmor]

it would be nice if the default was for all of the worksheets to be highlighted in the list box

Try this:

Private Sub Worksheet_Activate()
  Dim i As Long
  Dim sh As Worksheet
  Set sh = Sheets("Main")
  With Me.ListBoxSh
    .Clear
    For i = 5 To sh.Range("C" & Rows.Count).End(3).Row
      If sh.Range("AS" & i).Value = True Then
        .AddItem sh.Range("C" & i)
        .Selected(.ListCount - 1) = True
      End If
    Next
  End With
End Sub

The code fails if nothing is selected in the list box, I would like to get a error message that says "Select Sheets To Print" then ends the code.

Try this:

Sub Print_Sh1()
  Dim i As Long, c As Long
  Dim SheetArray() As String
  
  With ActiveSheet.ListBoxSh
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
        ReDim Preserve SheetArray(c)
        SheetArray(c) = .List(i)
        c = c + 1
      End If
    Next i
  End With
  
  If c > 0 Then
    If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
      Sheets(SheetArray()).PrintOut
    End If
  Else
    MsgBox "Select Sheets To Print"
  End If
End Sub

 

[Javi]

Sorry for the multiple post however I believe I'm almost there.

I am getting the message that nothing is selected however, the below code does not stop the original code it still runs.

Sub Print_Sh()
Dim i As Long, c As Long
Dim SheetArray() As String
Dim boolSelected As Boolean

With ActiveSheet.ListBoxSh
For i = 0 To .ListCount - 1
boolSelected = boolSelected And .Selected(i)
Next i
End With

If Not boolSelected Then
MsgBox "Nothing selected in listbox."
End If



With ActiveSheet.ListBoxSh
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
                               
                End If
                
        
        Next i
        
    End With

Sheets(SheetArray()).PrintPreview



End Sub

 

[DanteAmor]

I believe I'm almost there.

Don't worry, I already sent you the updates in post #7

 

[Javi]

Again thank you! The code to print works perfectly. If nothing is selected it gives me the message box just as requested awesome!


I also have a button for print preview and I tried to remove that part of your code unsuccessfully. Looking for the same results if nothing is selected they would get the message, otherwise it would show us the preview. Currently I'm getting the print selection pop up then the code fails.

Sub Print_Sh()
  Dim i As Long, c As Long
  Dim SheetArray() As String
 
  With ActiveSheet.ListBoxSh
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
        ReDim Preserve SheetArray(c)
        SheetArray(c) = .List(i)
        c = c + 1
      End If
    Next i
  End With
 
  If c > 0 Then
    If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
     Sheets(SheetArray()).PrintPreview
    End If
  Else
    MsgBox "Select Sheets To Preview"
  End If
End Sub