Display all worksheets of Workbook in listbox and print specific worksheet

How to display all worksheets of a workbook in a listbox and then print a specific worksheet is based on a question from one of our YouTube channel subscribers:

“Comment on your video: Print specific worksheet from Excel Workbook ‘There any way, when you click print a window said, select sheet to print: on the window you see all names from de workbook and you just want to click it. ¬†Henri

Henri most probably means whether there is a way to display all the worksheets of a workbook and then select a worksheet from the list for printing.

We insert a userform and the userform we place a listbox control and a command button.

The listbox is populated with the sheetnames from the workbook by using a ‘do until’ loop as shown in the complete macro code below:

Private Sub UserForm_Initialize()
Dim n As Integer
n = n + 1
ListBox1.AddItem Sheets(n).Name
Loop Until n = Worksheets.Count
End Sub

For printing a specific sheet we use a command button that is appropriately coded as shown below:

Private Sub CommandButton1_Click()
End Sub

As you can see the VBA code for both the controls is not difficult to understand.

Watch the Excel VBA training video below to see how the complete process is implemented:

3 thoughts on “Display all worksheets of Workbook in listbox and print specific worksheet”

  1. What do you have to change to print multiple sheets. I want to select the sheets. I want to print 2 or more sheets at one time? And how can i make a option to choose a printer before you print out?

    1. Worksheets.PrintOut – to print all sheets

      Sheets.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas)

      The selected printer will be used as the ActivePrinter

  2. Hello,
    I am trying to list data from multiple worksheets into my userform Listbox1 and within certain range of those worksheets as well. struggling to figure this out!

    Thanks for your help

Leave a Reply

Your email address will not be published. Required fields are marked *