Print Mark Dropdown with airSlate SignNow

Batch print esign

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)