Printing Userform Excel VBA

The following procedure covers how to populate an Excel userform with VBA to print a group of sheets or all sheets in a workbook.  This is a useful tool if you want to give your users the option of printing certain parts of the Excel file.  The abiltiy to control printing so your users can print a suite of reports is a nice to have.  There is not a lot of material on the internet on userform printing so hopefully this will add positively to the knowledgebase.

The following is my demonstration userfom.

There is always the option to use the file below as a template or you could insert a userform in the Visual Basic Editor and add the above controls.  If you do this each of the buttons at the bottom of the screen need some VBA coding to make them perform their task.

This is the task for the Print Option button (bottom left).  

Private Sub cmdOpt_Click() 'Excel VBA to check print option buttons and print.
Dim i As Integer
'Check that one option button is clicked on.
If Uf1.Opt1=False And Uf1.Opt2=False And Uf1.Opt3=False And Uf1.Opt4= False Then
MsgBox "Please select an option"
Exit Sub
End If

If Uf1.Opt1=True Then Sheets([{"North1", "North2"}]).PrintOut
If Uf1.Opt2=True Then Sheets([{"South1", "South2"}]).PrintOut
If Uf1.Opt3=True Then Sheets([{"East1", "East2"}]).PrintOut
If Uf1.Opt4=True Then Sheets([{"West1", "West2"}]).PrintOut
Uf1.Opt1=False: Uf1.Opt2=False: Uf1.Opt3=False: Uf1.Opt4=False
Uf1.Hide
End Sub

The Cancel button in the middle simply hides the form.

Private Sub cmdCanc_Click() 'Excel VBA to hide the form.
Uf1.Hide
End Sub

While the following Excel VBA procedure will print out all of the sheets in an Excel workbook.

Private Sub cmdAll_Click() 'Print the whole workbook with Excel VBA
ActiveWorkbook.PrintOut
End Sub

The following Excel file shows the above example with the relevant VBA coding.