Excel ActiveX Combo Box to Select Worksheets with VBA

let's take a look at a quick Excel macro that's going to improve the user friendliness of your files especially the bigger files that have many worksheets in them what we're going to do is this we're going to create a combo box that includes a list of all the worksheets in this workbook whenever the user makes selection they jump to that respective sheet now here comes the good part we're gonna do it in a way that's fully dynamic so anytime we add a new sheet we delete the sheet or rename a sheet the combo box gets updated automatically without us needing to do anything so no clicking buttons mapping first let's just start by adding a combo box to it so let's go to the Developer tab insert and select combo box from the active X control side and let's just draw it out we automatically go to design view first thing I normally do is go to properties and change the code name of the combo box to something that I recognize I'm gonna call this cb4 combo box sheet since I want the values inside the combo box to update automatically I don't want to fix it by specifying a specific list fill range I want to do that in the macro and normally whenever it comes to updating things automatically think about events and think about which events you could use to get this done there are specific events that are associated with ActiveX controls there are events associated with the workbook and with the worksheet so one event that comes to my mind that can be useful in this case is to use the worksheet activate event this would mean every time we activate this worksheet we could run the macro that's going to update this list now this can work because every time you go somewhere you're actually deactivating this sheet right so I'm going here I'm gonna rename this I add a new sheet or I delete another sheet my active action it is deactivated and when I go back here when I click on it I'm activating this I could write a code that's gonna loop through all the worksheets in this workbook get their names and add them to the combo box looping through each sheet in the workbook is easy right we can use a for each collection loop now adding them to the combo box it's also very easy because there is the add item method that we can use so let's go to a code view that we need to be on the code window of this worksheet which is called ActiveX and we are there so from this drop-down I'm gonna select worksheet default is selection change in this case I don't want the default I'm gonna select activate since we're planning to loop through each worksheet in the worship collection lets them as H as worksheet for each SH in this workbook the worksheets allow us to mix the sage we need to get the combo box name and to get intellisense I'm gonna do me so me stands for this object that I'm in which is the worksheet object dot this CB sheet dot do you see the right method here it's the add item method now what do we want to add to this SH dot name that gives us the the name that we see here of each worksheet let's test this let's go out of design mode first I'm going to deactivate this sheet so that I can activate it again to trigger that event now let's look there here if I currently make a selection nothing happens because we haven't written anything about what it should do if we do select something but check this out I'm going to click away and click back do you notice something everything is duplicate again every time you step out and you come back in it's adding items to the bottom of this list we don't want that so we should clear everything before we add any to this but let's get to our combo box and use the clear method on this okay let's just test quickly so now we have everything duplicated let's step out and let's go back and we just have it once okay that took care of that let's now write the part where we actually jump to the sheet that we select I'm going to go to design mode so that I can click on this and click on View code that automatically creates the procedure for the default event the default is change event I can go with the default in this case click event would work to change events works just as well the macro that we actually want is to select the worksheet name and use dot select so we can go with worksheets now the worksheet name we actually have it in the combo box so we just need to get the value of the combo box that's what was selected and say dot select okay so let's just test let's get out of design mode this jumped to data now when I go back I run into a problem subscripts out of range let's debug it has a problem with selecting now what's the value in the combo box it's nothing it's looking for a worksheet that's nothing it can't find it it runs into a problem we can add an exception here and say if CB sheet value does not equal to nothing then it should do this and right so otherwise you shouldn't run this let's see if this takes care of that I'm gonna step away go back to activate this now let's click on data I jump let's go back okay it's empty I didn't get an error now it would also be nice to just kind of inform the user what this combo box is for so instead of nothing let's keep default value of this to a text that says select a sheet right after the user makes their selection let's say CB sheet dot value equals select a sheet we set the value to that and then when we come back to this view we're going to make sure that the value doesn't equal to this because otherwise you can't select that sheet and only if it's not equal to this then it's gonna jump to the specific worksheet okay so let's see if this works let's activate this again click here we jump here let's actually add another sheet and delete a sheet let's go back okay we get selected she we didn't get an error Lila is gone and I have sheet 2 and sheet 1 can jump there and we can jump to sheet 1 so all this looks good until now but there is still one tiny problem with this I'm gonna show you what that is if we save this and now I'm gonna close this I closed that workbook I'm gonna reopen it let's say on another day it opens where I left it off and now I go to select the sheet and everything is empty why because the activate event doesn't trigger when the sheet is already active only triggers if I go back and forth and now everything is there to make sure this triggers every time you open it even if you had saved it when you were on the sheet and then you close it in this view what we can do is to create a workbook open event that checks to see if this worksheet is active if it is it's gonna click away and then click back here that's all it has to do it actually has to click away and click back here to activate it let's go to this workbook select workbook from here the default is open that's what we need and say if active sheet dot name equals active X in this case then it should just select another worksheet and then it should come back to this worksheet this makes sure that we trigger the worksheet activate event let's test again I'm going to save this I'm going to close open again I'm going to directly go here and it's triggered so that's how you can create a combo box that helps with the navigation of your larger workbooks this is part of my online Excel VBA a macros course if you'd like to learn VBA in a structured way check out the complete course link is in the description below you can also find it in the cards or go to X al plug comm slash courses [Music]

Channel: Leila Gharani

Views: 85,805

Rating: 4.9235182 out of 5

Keywords: how to fill combobox, select sheet from dropdown, deactivate and activate event, activex events, workbook navigation, ActiveX control, excel form controls, Excel dynamic fill of activeX control, workbook open event, excel macros, dropdown box, combobox, Excel Tutorials, Leila Gharani, Excel 2016, Excel 2013, Excel 2010, Advanced Excel tricks, Excel online course, XelplusVis, Excel tips and tricks, Excel for analysts, Excel VBA, Excel VBA tutorials

Id: bUMS_BCF08g

Length: 9min 56sec (596 seconds)

Published: Thu Sep 20 2018