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
Do
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()
Worksheets(ListBox1.Value).PrintOut
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:


5 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

  3. How to Display the Worksheets of any external workbook in Listbox. And print any of the worksheet throught listbox. Kindly help me!

  4. Greetings, I was just visiting your site and submitted this message via your “contact us” form. The feedback page on your site sends you these messages via email which is the reason you’re reading my message at this moment correct? This is half the battle with any type of online ad, making people actually READ your message and that’s exactly what I just accomplished with you! If you have something you would like to blast out to millions of websites via their contact forms in the US or anywhere in the world send me a quick note now, I can even focus on your required niches and my costs are very low. Send a reply to: Phungcorsi@gmail.com

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.