Excel List Box

to Display & Print Multiple Sheets

as ONE Print Job with VBA

This post will show you how to create an Excel macro that will allow you to print specific worksheets by selecting them form an automatically generated list.

Our goal is to create an ActiveX list box that will display a list of all sheet names in the workbook.  From there, we can select one or more sheets from the list and press a print button to print the selected sheets as a single print job.

We could also opt to display the selected sheets in a Print Preview window.  This will allow us to preview the entire print job in a single Print Preview window as opposed to previewing the selected sheets separately.

This post was inspired by a comment made by “Wayne” in a previous post titled “ActiveX Combo Box with VBA” for easier worksheet navigation (click here to view the video.)  In this post we will print the sheets and use a List Box instead of a Combo Box.

Let’s Set the Stage

We have a simple workbook with several sheets.

Some of these sheets are chart sheets and others are regular worksheets.

Creating the ActiveX List Box

In order to create the list box, we need to activate the Developer tab/ribbon.  If you do not have the Developer tab as a selectable option, right-click on any ribbon button and select “Customize the Ribbon…”.

From the Excel Options dialog box, place a check next to the choice for Developer in the right-side list panel.

We will now insert our ActiveX list box by selecting Developer (tab) -> Controls (group) -> Insert -> List Box.

Draw a box that approximates the size and location of the final object.  This can be resized and relocated later if necessary.

Excel will automatically place you in Design Mode.

Select the button labeled “Properties” (to the right of the Design Mode button).  We will change the following properties to aid in our development of the supporting VBA code.

  • (Name) – ListBoxSh
  • MultiSelect – 2 – fmMultiSelectExtended

The difference in the MultiSelect options are as follows:

  • 0 – fmMultiSelectSingle allows for only one listed item to be selected, ignoring traditional CTRL and Shift selection techniques.
  • 1 – fmMultiSelectMulti allows for selecting multiple items without the need for CTRL or Shift selection techniques. Each item is selected or deselected by clicking on the item.  This produces a toggle on/off behavior.
  • 2 – fmMultiSelectExtended allows for selecting multiple items using traditional CTRL or Shift selection techniques.

Populating the ActiveX List Box

We have two options available to us to populate the list box.

  1. Add a “Refresh” button to the sheet that will execute VBA code to regenerate the list box choices after a user adds/deletes/renames sheets.
  2. Create a VBA event-driven macro that will automatically regenerate the list when the user selects the sheet containing the list box.

Although option “1” would work well in a static environment where sheet changes are infrequent, we will go with option “2” to ensure the user will never be presented with an outdated list.

We will attach our code to the “Worksheet Activate” event associated with the sheet that contains the list box.

The code, when executed, will clear the existing list then loop through all the worksheets, adding the worksheet names to the list box.

Open the Visual Basic editor by right-clicking on the sheet tab holding the list box and selecting View Code.

You can also open the Visual Basic editor by pressing ALT-F11 on the keyboard, but the advantage of the right-click option is that it will open the editor and place you on the list box’s code sheet.  This feeds two birds with one hand (we’re not into killing birds with stones here at XelPlus.)

Select the worksheet object from the object dropdown list (left) and select “Worksheet”.

The default event for the worksheet is “SelectionChange”.  We want a different event to code against.   Select “Activate” from the event dropdown list (right).

Note: you can feel free to select and delete the starter code for the ”SelectionChange” event.

Action #1 – Clear the List Box’s Existing Items

In the code section for the Worksheet_Activate event, enter the following code:

Me.ListBoxSh.Clear

This is necessary to prevent duplicate items from appearing in our list box.

The reference “Me” refers to the current object; in this case, the list box.

Action #2 – Add the Sheet Names to the List Box

We will now loop through all the worksheets, adding each sheet’s name to the list box.

We need to hold the sheet’s properties in memory as we select them, so we will create a variable named “Sh” (short for “sheet”) by adding the following code to the top of the Worksheet_Activate event.

Dim Sh

After the line of code that clears the sheet, add the following code (explanation of code to follow):

For Each Sh In ThisWorkbook.Sheets

	Me.ListBoxSh.AddItem Sh.Name

Next Sh

This code will select each sheet, one at a time, and use the “AddItem” method to add the sheet’s name to the ListBoxSh object.

The completed code will appear as follows:

Private Sub Worksheet_Activate()
Dim Sh

	Me.ListBoxSh.Clear

	For Each Sh In ThisWorkbook.Sheets
		Me.ListBoxSh.AddItem Sh.Name
	Next Sh

End Sub

Are You Curious About VBA?

If you are curious about learning the various methods, properties, and events are associated with the VBA list box object, select View – Object Browser to open the Object Browser library.

From the search field, type “listbox” and press the search button (binoculars button).

From here, we can see the ListBox object in the Class column as well as various methods, properties, and events associated with list boxes.

Running Our First Test

To test the code that populates the list box, deactivate Design Mode (click the Design Mode button to toggle it to an “off” state) and select any other sheet in the workbook.  Return to the sheet that contains the list box to execute the worksheet activate event code.

The result should be as follows:

Testing for Updates to Sheets

To ensure the list is refreshed automatically when a sheet change occurs, add/delete/rename a sheet and then return to the list box’s sheet.  The list box items should have been updated to reflect the new sheet configuration.

Printing the Selected Items

in the List Box

In order to print the selected sheets from the list box, we need something to trigger the Print Preview or Print events.

Return to the Visual Basic editor (ALT-F11) and insert a new module by selecting Insert – Module from the dropdown menu.

In the Module1 code sheet, add the following code (explanation of code to follow):

Sub Print_Sheets()
Dim i As Long, c As Long
Dim SheetArray() As String

	With ActiveSheet.ListBoxSh

		For i = 0 To .ListCount - 1
			If .Selected(i) Then
				ReDim Preserve SheetArray(c)
				SheetArray(c) = .List(i)
				c = c + 1
			End If
		Next i

		Sheets(SheetArray()).PrintPreview
		
		'If you'd like to print out
		'Sheets(SheetArray()).PrintOut

	End With

End Sub

Let’s break down the code and explain what is happening.

Declaring our variables

Dim i As Long, c As Long
Dim SheetArray() As String

The variable “i” will serve as a counter to enable us to loop through the list of items in the list box.  We will define this as “Long” so we can accommodate files with up to 2-billion sheets.  This may appear to be overkill, so feel free to use Integer (32 thousand sheets) or Byte (256 sheets) for your specific project needs.

The variable “SheetArray()” will act as a temporary memory array to hold the names of selected sheet names from the list box.

The variable “c” will allow is to keep track of which “slot” in the temporary memory array we are storing selected sheet names.

Setting up our “With” block

With ActiveSheet.ListBoxSh
End With

Everything contained between these two lines of code will apply to the ListBoxSh object on the active sheet.  This will serve as a form of shorthand notation, so we won’t have to type “ActiveSheet.ListBoxSh” every time we wish to modify or read a property from the LishBoxSh object.

Looping through the items in the list box

In order to loop through each item in the list box, enter the following code in the newly created With block.

For i = 0 To .ListCount – 1
Next i

Because the list box starts counting from 0 (zero), we will begin counting from 0 and use the “ListCount” property to determine when to stop advancing through the list.  We must subtract 1 from the “ListCount” property to account for the 0 start point in our counting strategy.

Determine if a list box item is selected

If .Selected(i) Then

	ReDim Preserve SheetArray(c)
	SheetArray(c) = .List(i)
	c = c + 1

End If

This block of code will perform the following actions:

  1. Check the list box item designated by the “i” variable and determine if it was selected by the user.
  2. If the designated item from the previous step is a selected item, then
    1. Execute a ReDim Preserve command to extend the temporary array to the size defined by the variable “c”. The Preserve keyword will ensure we don’t lose any of the existing items in the array while we redefine its dimension.
    2. Take the currently selected item from the list box and place it into the temporary array in the last “slot” in the array.
    3. Increase the value of the variable “c” in the event we discover another selected item in the list box. This value will become the new dimension value for the array (if necessary).
  3. Advance to the next item in the list box designated by the variable “i” counter.

Check to see if the logic is correct

To ensure everything is working properly, an effective strategy is to activate the Locals window and then step through the code one line at a time using the F8 key on the keyboard.

This allows us to witness the counters advancing according to the number of sheets and number of selected items in the list box object.

Launch the Print Preview environment

Below the “Next i” line of code, add the following:

Sheets(SheetArray()).PrintPreview

This will send all the sheet names located in the temporary array we just constructed to the Print Preview routine.

Direct printing of selected sheets

If you wish to send the selected sheets directly to the printer, avoiding the Print Preview step, enter the following code:

Sheets.(SheetArray()).PrintOut

Adding the Launch Button

Excel has a designated macro launch button located in the list of Form Control objects.  Select Developer (tab) -> Controls (group) -> Insert -> Button.

Draw a button (rectangle) on the screen and select the print macro form the Assign Macro dialog box.

Rename the button to something more user-friendly, like “Print Preview”.

Test the macro launch button by selecting several sheets from the list box and press the “Print Preview” macro launch button.

If you wish to make your launch control look a bit more modern, consider adding an icon by selecting Insert (tab) -> Illustrations (group) -> Icons.

Consider resizing, moving, and changing the color of the icon to suit your needs.

Assign the macro to the icon by right-clicking on the icon and selecting “Assign Macro…”.

Select the print macro form the Assign Macro dialog box.

“Bullet Proofing” Code Tweak

In this example, a potential flaw in the use of the list box occurs if the user opens the workbook and starts on the sheet containing the list box.

The list box will lose its contents when the file is closed.

Since the list is updated when the user selects the sheet, the list of sheets will not have been updated since we opened the file on the list box’s sheet.

To ensure the list is updated upon opening the file, place the following code on the code sheet for the “ThisWorkbook” object.

Private Sub Workbook_Open()

	Sheets(1).Select

End Sub

This code will place the user on the first sheet in the workbook; in our example, this is the introduction sheet.  If your workbook places the list box on the first sheet of the workbook, set the value of the Workbook_Open event to a value other than 1.

Note: This code would not be necessary if you are using the manual approach to update the list box stated earlier in the post.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Unlock Excel VBA & Macros Course is here.

Save time. Achieve more.

Over 50 Excel macro examples for download & useful VBA codes you can use for your work.

Learn the WHY not just the HOW

LEARN MORE

2 Comments

  1. Terry Looyer June 5, 2019 at 11:34 pm - Reply

    Hi Leila

    I have been trying to make this work on Excel for mac 2016, but have not had any success.
    Are you able to to give some pointers as to how this could be done on the mac please?

    Thank you

    • Leila Gharani July 4, 2019 at 6:54 am - Reply

      I don’t have a mac, so I can’t test. Ron de Bruin has a great site for Excel for Mac VBA – maybe you can find something that will help you there: https://www.rondebruin.nl/mac.htm

Leave A Comment

Share This