Print Certain Sheets from a user form

Bump

All you would need for the first part would be a way to populate with only the names of the 4 sheets.

How to do that really depends on how you determine what those sheets are.

Can they be distinguished from all the other sheets by something? eg name

Yes, the name of the sheets are Waybill, Commercial Invoice, Packing Slip, Labels.

If I could just figure out how to get a user form to give me 4 check boxes, one for each sheet, and print the sheet that's assigned to each check box I think I could add the other features I need from there.

Any ideas?

Try this.

Create a userform, add a listbox and a command button.

Add this code to the userform module.

This code won't print the worksheets as it is but to do that just needs a few lines of code, maybe even one.

Also I don't know how you actually want to print the worksheets, all in one go, separately etc.

OK, everything looks good. It is a little different than I was thinking but it will work just fine. The problem is this, I can do what you have there with out a problem. What I need help with is figuring out how to get the data from the listbox in the userform to perform the print job. If I choose "Commercial Invoice" which is record 2 in the Listbox, how do I transmit that to a command telling Excel to print he sheet titled "Commercial Invoice"?

Something like this although I know there has to be a better way to do this:

Code[]
Dim Ps, Ci, La, BOL, Pf, Printer As String
Ps = CheckBox3.Value
Ci = CheckBox1.Value
La = CheckBox5.Value
BOL = CheckBox2.Value
Pf = CheckBox4.Value
Printer = Label1.Caption

If Printer = "" Or Printer = "Not Found" Then Exit Sub
Application.ActivePrinter = Printer
If Ps = True Then
Sheets("Packing Slip").PrintOut
End If
If Ci = True Then
Sheets("Commercial Invoice").PrintOut
End If
If La = True Then
Sheets("Labels").PrintOut
End If
If BOL = True Then
Sheets("Waybill").PrintOut
End If
If Pf = True Then
Sheets("Printable Form").PrintOut
End If
'ActiveSheet.PrintOut
Unload UserForm1
End Sub
Code[]

Jeff

Did you try the code I posted at all?

The worksheet names you specified are listed in the listbox - so use that list to get the names of the ones to print out.

I did, but I'm not sure how to extract the names from the Listbox. What is the value in the list box? Is it the value of what is selected? What if there are multiple names selected?

Does that help explain my confusion? I'm not sure where to start.

Jeff

It's pretty straightforward to get the value or multiple values.

What you do for a multiselect listbox is loop through all the items in the list and check their Selected property.

If it's true then the item has been selected and you can get the value of the item using the listbox's List property and the ListIndex of the item.

One thin to be careful about is that listboxes are 0-based.

Sorry I can't post any code - if I don't stop typing soon this machine might be on a collision course with a badly-tuned TV.