Userform to Select what worksheets to print

[paul mullan]

Hi I want to creat a userform that has 2 list boxes and a command
button.

- The list boxes are to identify a range of worksheets to print
- the command button will raise the print options menu (allowing you
to select which printer to use) - I am actually using cutePDF to write
these files to a PDF.
- I need to print the same range on each worksheet (A1:AQ104) and the
print to be scaled to one sheet of paper and centered hoz and vert.
- I need the output to be a single PDF file with however many pages I
have selected, in the same order as they appear on the workbook

I have looked at several solutions that have created a userform to
select one worksheet but none that use two listboxes to specify a
range.

Anyone attempted somethign like thios before or care to offer your
help.

Much appreciated in advance!

Paul

 

[Ben McClave]

Paul,

I'll leave the print settings to you, as I'm sure you'll be able to work out getting the range to one page that is centered. However, assuming that your userform has two comboboxes (ComboBox1 and ComboBox2) as well as a CommandButton, the following code should do what you need (place on the UserForm code module).

Hope this helps,

Ben

Private Sub CommandButton1_Click()
Dim lCbo(1 To 2) As Long

If Len(Me.ComboBox1.Text) < 1 Then
MsgBox "Please choose a start sheet."
Me.ComboBox1.SetFocus
Exit Sub
ElseIf Len(Me.ComboBox2.Text) < 1 Then
MsgBox "Please choose an end sheet."
Me.ComboBox2.SetFocus
Exit Sub
End If

lCbo(1) = Sheets(Me.ComboBox1.Text).Index
lCbo(2) = Sheets(Me.ComboBox2.Text).Index

If lCbo(1) > lCbo(2) Then
Call printsheets(lCbo(2), lCbo(1))
Else
Call printsheets(lCbo(1), lCbo(2))
End If


End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Me.ComboBox1.AddItem ws.Name
Me.ComboBox2.AddItem ws.Name
Next
End Sub
Sub printsheets(lFirst As Long, lLast As Long)
Dim l As Long

For l = lFirst To lLast
On Error Resume Next
Sheets(l).Range("A1:AQ104").PrintOut PrintToFile:=True
If Err.Number = 1004 Then
Err.Clear
Else
MsgBox "Unhandled error: " & vbCr & vbCr & _
"Error number: " & Err.Number & vbCr & _
Err.Description
Err.Clear
End If
Next l

Unload Me

End Sub