Today we're gonna create an Excel macro that's gonna help us
print specific worksheets by selecting them from a list. So we're gonna create an ActiveX list box that's gonna include a
list of all sheet names. We can select one or more Excel sheets, press print, and it's gonna print them as one print job, or we can send it to print preview and be
able to view everything in one print preview screen
and not separate ones. This video was inspired
by Wayne from his comment on a previous Excel VBA video, which takes a look at creating a combo box for easier worksheet navigation, except this time we're
gonna look at printing the selected sheets in
one go and we're gonna use a list box instead of a combo box. (calm music) I have a sample workbook
here with different tabs. Some of them are chart
sheets and some of them are normal worksheets. So first thing I'm gonna do is to insert an ActiveX list box, so let's
go to the developer tab. If you don't have the developer
tab, activate it here, just right mouse click
anywhere on the ribbon, select customize the
ribbon, put a check mark beside developer, and then click on okay. Inside the developer
tab, let's go to insert. From ActiveX controls, select the list box and then just draw it
out where you wanna have your list box. Excel automatically
takes you to design mode. So what I normally do here
is then go to properties, change the properties
I need for my list box. I usually change the name of my ActiveX, I'm gonna call this
listboxSH just so that I can recognize it easier later on. The other thing we need changed here is the multi select
property, because by default, only single selection is allowed, but I wanna be able to multi select. So I have a choice between select multi or multi select extended. I'm gonna go with extended
because here you can also use the shift and control keys. Now let's talk about
filling this list box. I don't want it to be
connected to any cells, I want it to include the
names of my worksheets. So I have different
options, either I can add a refresh button here, so
any time I add new sheets or I rename some sheets,
before I come to print I can click on refresh and
it updates the names here. Or I can connect this to a special event. In this example I'm gonna connect it to the worksheet activate event so that any time this
worksheet is activated, so any time I go out of this
sheet and I come back here, the macro automatically runs. That macro is gonna clear
everything in this list and then it's gonna
loop through each sheet in this workbook and add the name of it to this list here. So let's do that real quickly, we can bring up the visual basic editor, but since I'm on this sheet right now let's just right mouse
click, click on view code. This automatically takes
me to the code window for this specific worksheet. So what I want from here
is the worksheet object, default event selection change. That's not the one I want, I
want the activate event here. So one thing I wanted was
to clear that list box so I'm gonna go with me
dot, so me is this object I'm in right now which is the worksheet, listboxSH dot clear. Okay, so before I add anything to this box I'm gonna clear what
is in there, otherwise I'm going to be adding the
sheets multiple times. Every time I click away
and back they're going to get added to that list. Then I want to loop through each sheet, so let's dim that. I'm not gonna dim it as
worksheets because I also have chart sheets here. So next let's loop, so for
each sh in this workbook dot sheets, right, not worksheets, sheets, and let's just close this so next sh, so what I want to happen
is to add the item to the list box. So let's go back to our list box object and let's choose the method add item. What do I want to add? The sheet name so SH dot name. That's pretty much it. If you're curious to find
out what other methods, properties, or events are
available for list boxes, you can check that out inside view in the object browser. If I just type in list
box here, press enter, right here I can see the class list box, here I can see the different
methods and properties and events associated with list box. This one with the green
icon, these are the methods. We already used the add item method and the clear method. These ones with the hand icon
are the different properties so we're gonna be using soon the list and the list count property. These ones with the yellow lightning icon, these are the different
events that are available for list boxes. So to find out more
about any object in VBA, you can always come to the object library. I'm just gonna go back to the code. Now let's go and test this. First off let's get out of design mode. Now to run my code, I need
to run the activate event. Just need to click away,
click back and I get the list of tabs added here automatically. So let's just check this. If I just change this from chart one to just chart four, and go back here, I can see that updated
automatically in here. Okay, so we figured out
how to fill this up, we want to be able to
highlight different items, right, which I can do by
holding down the shift key, I wanna be able to print them. So I need something to trigger that event. I'm gonna use a button
for that, but first off before I add the button,
let's write the macro. So let's go back to
Visual Basic, Alt + F11, I'm gonna add the macro to a module, so let's go to insert, module. By default it added
module one, let's create a new sub-procedure here. So let's think of the different variables we need right here. One variable is to loop
through each name in this list. I'm gonna dim I as long. I can also dim it as byte, hopefully we don't have
that many worksheets, just to be on the safe
side I dim i as long. Now the other thing that
we need is a string array because I want to keep the
sheet names in memory. The more sheets I've selected, the bigger it's gonna get, so I'm
also going to dim C as long. So I could dim this as byte as well. Next for the sheet names I'm gonna dim sheet array, bracket open bracket closed 'cause I don't know its
size so I can't define it right here, and I'm
gonna dim it as string. Next let's use with active
sheet dot list box SH and let's do end with right here. I need to loop through
each item in the list box so that was my I, so for I equals zero, now why zero, because
the first element inside the list box has an index of zero, right? Not one, but zero. Two, now that depends
on how many tabs I have. I could count them in different ways, I'm gonna use the list count property of the list box and then I
have to make an adjustment of minus one because the index
of the first item is zero. So since I've opened this
loop, let's just close it so next I, as I'm looping I need to check if anything is selected or not. If dot selected I, then what should it do? Let's close this with end if. Now comes expanding my array. Because I need to keep what it found in the first round, I
need to not just re-dim it but I need to preserve
what it already found. And I'm gonna use sheet array C. To add it to my array, I'm
gonna use sheet array C equals dot list i. Right, so that item is gonna be added to my string array. And then I'm gonna add
one to my C variable. Before we do the print
or print preview command, let's just make sure
this part works properly. I'm gonna go to view and
bring up the locals window because here we can
really check if everything is working as we like. So I'm gonna click F8
to step through this, I can see my variable's created here, I and C have default value of zero, sheet array's empty for now. I is zero so it's running
right here on the first item. It's not selected, right? So it's not gonna go
inside, it's just gonna go to end if and run again. Second one, not selected,
third one is selected, it's going in, my I has
an index value of two. Remember, this was zero, one, two. Now let's see what happens to this. Sheet array, something is getting created. It got the name chart four. Right, so C now equals C plus one, next one is not selected, not selected, this one is selected. So C's gonna get a two, and my sheet array is gonna have input form in there. Let's just put a stop
here and run this to here and see what value we get. Our sheet array has three
items in there and those are the three items we
can see right there. Right, so that's perfect, let's stop this, let's remove our break point. Now all we have to do
is add the print command or the print preview command. I'll do the print preview for now, let's see if it works properly. So sheets array is sheet
array, open bracket close bracket, dot print preview. Let's just run this to test. That's my first tab,
that was the chart tab, that's the second tab,
and that's the third tab so three out of three is all in one view. Right, so if you wanted to print this out, you would do sheets,
sheet array dot print out. Okay so that will print
it out as one print job. I'm just gonna comment this
out for now because I don't wanna print anything right now. The only missing part
here is to add a button that's connected to that macro. So since my macro is in a module, I'm just gonna add the usual form control button, click on this, click on okay,
and let me just rename this, select this, this, this, and this, click on print preview. I have one of four right here. Okay so in the same way
you can attach your macro to an icon, if you wanna make it look a bit nicer, a bit more
modern and fancier. Click on insert, change
the color as you like and then right mouse click, assign macro and assign the macro to your icon. So when you click it, it
goes to print preview. One tweak you can add to
this is to add a workbook open event to this workbook object. Now because I'm using the activate event to fill up my list box, the
problem I'm gonna run into is that if I save the
workbook while I'm in that sheet where I have
the list box and then I open it again, the list
box is gonna be empty because the activate
event is not gonna run, it's only gonna run if I
switch my view somewhere else and then switch back. So what you might wanna do is to add a simple selection code to this. I'm just selecting
sheets one and sheets one is another sheet here, it's not the sheet where I have the list box. It's my introduction sheet. So this makes sure that
to get to the list box, they have to click on the
tab which is gonna run the activate event. If you happen to have your
list box on the first sheet, you can switch to sheet two
and then back to sheet one. This way you're gonna
run the activate event. Now you don't need this if
you go with our original option one which was to use a button to fill up the sheet names. So that's how you can
use an ActiveX list box together with Excel VBA
to select the sheets that you want printed
and print them in one go. If you like this video,
click that thumbs up and if you wanna become
more advanced in Excel, consider subscribing to this channel and joining our wonderful community here. (calm music)
