Macro to cycle drop down list print 2 tabs to PDF and save

Lets 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 boxs code sheet. This feeds two birds with one hand [were 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 Boxs 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 sheets name to the list box.

We need to hold the sheets 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]:

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

The completed code will appear as follows:

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 boxs 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]:

Lets 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 wont 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

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 dont 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 boxs sheet.

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

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.

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