Excel - Userform with option buttons to print selected sheets

Asked By Roger on Excel on 30-Dec-09 11:16 PM
Can anyone help me with code to help me buid a userform in which a user
selects the sheets in the workbook to print using option buttons ?

Once the user has selected the sheets another button prints them

Can anyone help?

Thankyou,

Roger




Jacob Skaria replied to Roger on Excel on 31-Dec-09 01:40 AM
How about using a combo box. Place a combobox and command button in a
userform and try the below code


Private Sub CommandButton1_Click()
ThisWorkbook.Sheets(Me.ComboBox1.Text).PrintOut Copies:=1, Collate:=True
End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Me.ComboBox1.AddItem ws.Name
Next
End Sub

--
Jacob
Harald Staff replied to Jacob Skaria on 31-Dec-09 02:27 AM
And if you want multiple choice, use a listbox. Modified code:

Private Sub CommandButton1_Click()
Dim L As Long
For L = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(L) = True Then
ThisWorkbook.Sheets(Me.ListBox1.List(L)).PrintOut Copies:=1, Collate:=True
End If
Next
End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Me.ListBox1.MultiSelect = fmMultiSelectMulti
For Each ws In ThisWorkbook.Sheets
Me.ListBox1.AddItem ws.Name
Next
End Sub

HTH. Best wishes Harald
Roger on Excel replied to Harald Staff on 31-Dec-09 01:04 PM
Thanks Harald,

This solution with the list box is perfect for me

Best regards

Roger