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.
- 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.
- 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.ClearThis 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 ShAfter 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 ShThis 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 SubAre 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 SubLet’s break down the code and explain what is happening.
Declaring our variables
Dim i As Long, c As Long
Dim SheetArray() As StringThe 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 WithEverything 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 iBecause 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 IfThis block of code will perform the following actions:
- Check the list box item designated by the “i” variable and determine if it was selected by the user.
- If the designated item from the previous step is a selected item, then
- 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.
- Take the currently selected item from the list box and place it into the temporary array in the last “slot” in the array.
- 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).
- 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()).PrintOutAdding 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 SubThis 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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
