Excel VBA Forms Part 6 - ComboBox Controls

welcome to about six of this Excel VBA user forms tutorial this partner series is going to talk about how to use combo boxes or drop-down lists as they're better known so I'll start by showing you how to draw in format combo boxes and then how to populate them using a static list well then write some code that allows you to refer to this selected value and pass that up back out into the worksheet and then we'll move on and show you how you can use dynamic lists and how to add items and clear the list while the forms running the last part the video is going to talk about how to use multiple columns and how to reference the values in those multiple columns when the form is running so quite a lot to do here let's get started so to demonstrate how drop-down lists work we're going to extend our basic data entry form to allow users to select which certificate has been awarded to a film so you can see that we've already extended our original list on the sheet one worksheet to include a certificate column and we've already entered some values against the film's but if I happy waiting therefore 1280 films for there you go and we've also just to help ourselves out a little bit later on created a new lists worksheet in the workbook and in there we've actually listed out all the available ratings for all these are the BBFC ratings so i'm cited if into the MPAA if you're in america so we're going to use these ratings to populate our drop-down list but before we can do that we of course need to draw it so we need to go back to the developer tab and head to the visual basic editor and with the form open we're going to draw a combo box control on the form I'd like to add my combo box into the same group of controls as the basic information so in the same frame so I don't want to spend too much time doing this as we've spent an entire video talking about drawing controls already so I'm going to rearrange things a little bit by increasing the I to the form move my buttons down a little bit I'm going to increase the height of my frame now the combo box tool sits inside the tool box just next door to the text box so that's what it looks like I can click once in that tool and then click once on the form to draw in the same way of drawing any other control I'm going to give it a sensible name so I'm going to call this one film certificate that's the value that is going to store spell certificate to properly again there we go super and then the next thing I'm going to do is add a label that corresponds to that combo box so back to the tool box I'm going to draw a label which is just next door to it and I'm going to follow the same sort of naming convention as I follow for all my other pairs of controls and label I'm going to call this one film a certificate label so certificate label this just make it easier to apply the validation to this control ultimate later on the caption for my label I'm just going to enter the value certificate in here I'm making sure I spell it correctly there we go and then a little bit of actual work just to make sure the consistent this consistency in the layout I want to make sure that my label height is the same as the height of my previous labels so I'm going to select a couple of different labels so the release date and certificate I want to make sure that release date is the dominant control and then edit the format menu and choose make same size height so that all matches what I'm then going to do is make sure that my combo box lines up with all the text boxes so it looks as though it already does actually but just to make absolutely certain I'm going to let all the text boxes one of them is dominant they are the text box at the top make sure I've aligned the left of those then I just want to make sure that the label is aligned to the middle of my combo box so make sure make sure I've got selected both make the combo box the dominant control and then align the middles of those and there we go there's our basic combo box drawn on the form now it shouldn't be too big a surprise that once you've drawn the combo box it's not actually populated with any values yet so if I clicked on the drop down arrow obviously I got a zone there we go I'll see that the drop down this is empty so there are several ways to populate a combo box I'm going to show you the quickest shortest simplest using a property called row source so if I select the combo box I've got a property in the prodigy window called row source the basic idea here is that you enter a string of text which represents a range of cells containing the values you'd like to assign or to populate the combo box with so I'm going to switch back to excel just really quickly and show you the the range of cells we're going to be using so this is on a lists worksheet and I'm going to use cells a2 down to a7 so a simple way to populate that row source property is to enter a string a text referring to that range of cells so I need to reference it as lists that's the name of the worksheet then an exclamation mark to separate the worksheets name from the range of cells then simply a2 colon a7 once I've done that if I click on the drop down arrow again on the form our final list is populated now instead of using cell references to set the row source property you can also use a range name if you've created one so to create a range name I'm going to switch back into Excel and then there are many many different ways to do this you may have seen a few in the previous videos that we've made but one simple way to create a range name is to select the range of cells that you want to give a name to and then click into the name box at the top left-hand corner the worksheet and then simply type in your name and we'll call this on BBFC of ratings the important thing is you don't use any spaces in the range name and also that you press Enter once you've finished typing so that's created a range name that refers to that block of cells so I'll see I'll be able to select that range and it refers to you cells a2 to a7 on the lists sheet that's just one way to create them there's many many other ways but that's a quick and simple simple way so now that I've created our range name I can switch back into the visual basic editor and with that combo box still selected I can head down to the row source property I'm going to place this slightly convoluted reference with BBFC ratings and that will have exactly the same effect so I'd reference the cells by their reference personally I find the reasoning is slightly easier to use so that's what I tend to do myself but the choice is yours the next thing I'd like to do is make sure that when the user clicks the add to list button whatever item they've selected from the combo box gets added the list on the worksheet so to do that we need to go to the code behind the click event of the add to list button we can do that by double clicking on it and then if you remember in the previous video we really factored this code to make sure we separated out the code that adds the data to the list so rather than scrolling up and down to find this separate procedure a simple trick here is to right-click on the procedures name and then choose definition and that will take you directly to the top of that procedure referring to the value of a combo box is pretty much identical to referring to the value of a textbox so we're going to add in a couple of extra lines of code here I'll separate them out just to make them stand out I'm going to refer to the value of the cell that's three columns to the right of the active cell that's where the certificate value is going to go I'm going to change the value of that cell to be equal to film certificate dots per value so just like a text box a combo box got a value property it's also got a text property as well just like a text box has and we're going to use the value property it doesn't really matter which one we choose but I'm gonna use value just to keep things consistent and then just as we've done with the text box values that we've added in we're going to apply the same formatting to the new value so I'm going to refer to the active cells offset zero going three dots a number format and then I'm going to make that equal to the number format at the first cell in that column so that's going to be range III and dot number format and get my reference in garage there we go rinji three dot number format okay so now that we've added that code we instantly test out if that works back in Excel so to test the code we're going to head back into Excel and then we'll go to our menu worksheet and we'll display our four months reading by clicking one of the three buttons and let's add las a Deadpool again to list so we say the pool and the gross was home seven genes that you were million dollars so far apparently release date was as well that Feb 2016 and the certificate um to use this drop down this you can of course click on the drop down arrow with the mouse no problem and you can also while the controls got the focus you can use the keyboard to do it as well if I hold down the Alt key and press the down arrow key that expands the list and then I can scroll up and down little list with the cursor keys there was a fifteen rated film in the UK so is a lot to say naughty words in the film so there we go let's choose fifteen and then I can add the film's at list either by clicking my button or use my keyboard shortcut alt and a and there we go Diplo gets audited list and the value of the combo box gets added to the appropriate column as well so at a basic level comma box is not much more difficult than text boxes is certainly in terms of referencing women code and they've got this extra feature you have to populate them with with some values but about in that they're pretty straightforward one small problem with combo boxes by default is that although they look like they've got a restricted list of discrete values to select from there's nothing to stop you from typing in whatever you want if I go to the certificate drop-down this I can type in literally anything and the text box the combo box will accept it so there's no restriction by default on what you enter so that's a problem we need to solve and we can do that in one of two different ways if I close down the form and then go back to the visual basic editor there are two different properties of a combo box that you can change to restrict the user to only those items you've provided in the list one of the two properties you can change as a fairly obvious name is called match for required so if I scroll down through the properties list and find much required you'll see it set to false by default if I double click that little switch to true and the effect of that if I switch back into Excel is when I show the form and I try to select some of those not in the drop-down list as soon as I try to move away from that control to set the focus to something else I'll get this little pop-up message informing me of an invalid property value now there's nothing I can do to modify that message that appears as far as I'm aware as just a built in implicit error or a warning message all I have to do is click OK and then select an item from the list and then things are fine personally I don't like that style of validation where you have to click on a separate message before you can fix the problem that's been caused so there's an alternate way you can do this rather than using the match required property if I close the form down and go back to the visual basic editor select the combo box again I'll set the match required property back to false now the name of the other property doesn't give you much of a clue that it can control what users can select in the drop-down list the properties name is called style so I scroll down through the properties window and find the style property there it is we'll find that the default option for a drop-down list is FM style drop-down combo I believe the word combo comes from a combination of text box and drop-down list so there debiting that you can cite an item from the list or type in a value you want well we want our drop-down list to behave purely as a drop-down list not a combination of a text box in a drop-down so if I select from the style property the only other available option FM style drop-down list the effect of that is when I go back to excel and then attempt to launch my form if I click into the combo box I'll find that I can select an item from list but I can't just type in whatever I want if I try typing nothing will happen so that's a much better way to validate it means I have to click OK on the message box I simply I'm not allowed to type in any value that I want so I set up the style that I prefer one other thing I'd like to do in terms of validating the combo box is make sure that a user has selected a value before they add the data to the list so we can achieve that by making a quick modifications at the code we wrote in the previous video to check that all the text boxes I've been building I double-click on the add to list button the the everything filled in function I just scroll down a little bit is checking that all of the text boxes have been filled in so what we're going to do is make a quick modification to the if statement here to make sure that it checks for combo boxes as well so as well as checking if the type of control is a text box I'm going to add an or statement um as a or type of CTL is MS forms dot combo box what that means I'm going to have to do now is make a couple of other changes we have did this little bit of extra code in the previous video to sister the result of the control that the control variable in a variable that can only hold a text box and that was purely to make the intellisense list work to refer to the value and the back color of the object well I don't want to do that anymore in fact I can't do that now but if I'm establishing that this is a combo box then I can't store a reference to a combo box in a variable that can only hold a reference to a text box so I'm going to delete that variable altogether and then revert to using just the CTL variable so let's take out lines has set T XT equals CTL and then change this so we refer to the CTL value and the CTL back color everything else references the CTLs name so in that case this will be absolutely fine so quick simple it will change to incorporate the other combo box the main reason this works of course is that the combo box was drawn inside at the film details frame if I join it outside of the film details very mind I'd be having to change this loop as well to make sure it looped over all of the controls on the entire form I could say meet all controls but fortunately we do have to do that because I drew the combo box inside the frame so to give this one a quick test all I should need to do is go back into Excel and then show my form on screen and try to add an item to the list by clicking the button without forthing anything in and you see quite happily that again the background color and the Associated label are changed when the when the comment box haven't been filled in they'll just select an item here and then try to click the button again the other thing we haven't done yet is change the background color and the label color back to their original ones so let's just add in a little bit of extra code to achieve that as well to reset the formatting of the combo box and it's labeled back to their original States we're going to take the same approach as we did with the text boxes in the previous video so if we switch back into the visual basic editor we're going to access the after updates event of this combo box if I give that common box a quick double click it will give me the default event for the combo box which is the change event that's not quite the one that I want so go again going to the drop-down list at the top right hand corner I can select any of the other events of that object so the one I'm going to choose is after update and then in there and you can writing the code that's going to change the formatting of that object back to its original status so I'm going to refer to film certificate dot back color I'm going to make that equal to RGB white and then we can revert to the film certificate label so film certificate the label dot for color I'm going to make that equal to the for color of the entire form so me dot for color is a simplest way to achieve that and of course I only I only want to make sure that works if I have entered a value into the certificate text box our certificate combo box so I'm going to add a quick if statement around this that's going to say if film certificate dot value is not equal to an empty string then change the colors and then don't forget the end if at the end okay so to give this one one final little test in terms of validation head back to excel and then we can show the form on screen again and if I click Add to list everything will change color if I choose magnums the drop-down list and tap away from this control then it will revert back to its original colors so perfo there's all the validation code working just before we move on and talk about a couple of the more sophisticated data properties of the combo box I want to quickly mention one or two the simple formatting properties so things you can use is sort of modify their the drop-down list style and exactly how the options appear in the list so there's a property of the combo box called drop a button style you can see by default is set to drop button style arrow there are a couple of other choices in here I'm so you can choose button style plain which removes any symbol from the button all together and there's it ellipsis which is the dot dot and there's also the reduce which is a little little underscore icon they don't do anything to affect what happens when you click on the button is purely to do with how that value guess I'll sorry how the button gets displayed so I think it kind of makes sense out the drop-down arrow style for this particular example one other thing we can do is change the list style which affects the display of items inside the list so there's a list style property here which is set to plain by default there's only one of the choice in its list of style option so going doesn't do anything to affect the way values are selected but if I click on the drop down arrow now you can see that each one has a little dot symbol next to it so it's sort of and needs a way to identify or indicate that each items being selected so um it's very quick simple couple of properties just to show you a couple of simple things you need to modify the appearance if your drop-down lists earlier in the video we use the row source property to set what range of values will be available in the drop-down list and the property works fairly simply you just enter a string of text representing either a range name or the cell references of the cells which contain the bodies you want to select a small problem with the row source property is it's fairly inflexible once the form is running you can't add new items to the list or delete existing items you can clear the list entirely and then reset the row source property but that's a little bit of overkill so what going to show you a slightly more flexible way to populate drop-down lists using the list property so what going to is first of all remove the the row source property the BBFC ratings region in from there and then we're going to add a bit of code to the forms initialize event to add items to this list the initialize event of the form is probably the best place to do this so to get to there let's just quickly double click on the background of the form and because I've already used the user form initialize event previously it takes me directly to that event I've got some commented out lines of code here that set default values for the film gross and film beta text boxes so much going to remove those altogether and then we'll replace some lines of code here to populate our combo box instead it's really worthwhile mentioning that you can just set the row source property in code so if I wanted to I could say film and certificate dots of row source equals and then I can use a string of text I can say BBFC ratings and as soon as the form is loaded that would automatically set the row source property but as I said earlier on you can't then subsequently add new items or remove existing items from the list so that's not quite what we want to do let's comment out that line instead what we're going to do is use the list property so the list property is subtly different if I say film certificate dot sorry film certificate dot list equals what I need to do here is refer to the value property of a range of cells so I can say range and simple thing to do here is use the the range name that I created earlier on which is BBFC ratings although of course I could use any other range of cells cell references and then say dot value so to the end user to begin with there will be absolutely no difference in the way this works if I switch back into Excel quickly and shoot to show my form on screen I'll get exactly the same list of values that I had last time with a row source property now one of the useful things about the list property is that it allows you to refer to a range of cells relatively as opposed to the row source property where you have to enter a string of text representing a fixed absolute range of cells so that can be useful on your list of values which populate your combo boxes changes frequently so if we imagine for instance the BBFC rating system is going to change regularly we might have a different number of items in this list each time so for instance there is actually another another rating for the BBFC is called r18 which is for very naughty films we won't talk about those and but just having a new item to the end of the list doesn't incorporate that inside the BBFC rating range name so what we have to do the row sort property is either change the way the range name works or we'd have to update the row source property in the forms design with the list property however we can write some simple code that refers to all the values in this list from range a - down to whichever cell sits at the bottom of the list from range a - so let's go back to the visual basic editor and write some code that will allow us to do that so back to the initialize event of the form and we're simply going to add change this line which has film certificate a list equals so let's start by removing the range of cells that refers to the BBFC ratings and we'll replace that with a relative selection so we must say and we'll refer first of all to the worksheet lists source AWS lists dot range a - which is the top cell in our list put by a comma and then to refer to the bottom cell in the list from range a - downwards we can say WS lists a dot range a - dot end Excel down stick a dot value on the end to refer to the values of those cells and just we can see this in one single screen with let me write this one across two separate lines so the top cell that were referring to in our list is range a - on the WS lists sheet and the bottom cell were referencing in the list is the cell that's at the end of the list and a downwards direction from range a two on the list sheet we've dealt with this in so much detail in the in the excel vba tutorial series and there's an entire video all about selecting cells I think it's part number five in that in that series so if you're not sure about how this works feel free to go back and watch that video but just to prove that it does work what we can do now is go back into excel we display the form on screen from our menu we can click any of these buttons we should find that now we've added in this extra rating system based on the values already in the list the great thing is that that adapts automatically based on how many items are in the list so if we added ratings x y&z then those would automatically be included - the next time we show the form likewise if we remove items from the list in the end of the sheet those would also be removed from the from the list of ratings when we load the form again so a nice dynamic flexible way to set the values in a drop-down list if for some reason you don't want to have to store the values for your drop-down list somewhere in a worksheet you can always write code to add items individually so heading back to the initialize event of the form what we're going to do is actually let's write this in a separate subroutine altogether just so we can quickly deactivate it later on I'm gonna write a separate private sub that will let's see that will add or populate certificates so let's say populate our certificates I'm going to do this by adding items individually into the certificate list so to do that we can simply say film certificate dot add item and then I can just reference the value that I want to pass in so let's add in you first of all and then I can just happily copy and paste that line several times it might make more sense to use with statement here actually so let's have a with statement well say with films are difficult add item and then end with and then I can simply copy and paste the add item section just to avoid replicating more code than is absolutely necessary my current family I need so let's find out so you PG 12 a 12 15 and 18 there we go got it right so you go I'll miss out the last one the are 18 I don't really care about that one so having done that rather than setting the list property in the initialize event of the form I'm going to comment out those two lines there what we can do instead is just call on our populate certificates subroutine sometimes they call populate certificates again they call keyword is optional you don't have to enter that I'm gonna leave it in there just I think it makes a nice way to indicate that you're calling a procedure you've written rather than something that exists in VBA already so having done that if we switch back into Excel and we have a look at the form once again it should be populated with the same list of values so that would mean we can get rid of our lists sheet all together we don't dis store those values anywhere in the worksheet anymore just for convenience though I'm going to go back to my VB editor again and I think it makes more sense for this particular example but one does everybody I think makes more sense for the particular example to populate the list based on the values of the cells so I'm going to uncomment those I'm going to remove the line which caused my populate certificates subroutine and then leave it as so it's also worth knowing that you can update the list of entries in a drop-down list when the form is actually running so what I've done here is I've come back to the lists sheet and I've added in the MP double-a rating so these are the the American rating systems for movies so I've entered these values into cells now given this block of cells a range name and what I'd like to be able to do is switch between these two rating systems when the form is actually running so heading back to the visual basic editor I'm going to do this in a fairly simple way to begin without showing a couple of simple buttons inside the frame that let me click between the UK and the US rating system so let me just move these and that them interesting increase the size of my form first of all move these buttons down a little bit further and then increase the height of my frame and I'm going to add in a couple of simple little buttons command buttons again I have tools we're familiar with already and then I'm going to add in a similar one that's a similar size I don't care too much about the formatting here what I will do is change the name and the caption of these buttons so let's change the name of the first one so that it says use UK ratings and then the second one that will say use US ratings and then the caption of this will be us nice and simple and the caption this one will be UK the clicked event of these two buttons very very simply all we're going to do is update well first of all we're going to clear the contents of the existing list first using the clear method and then we're going to reset the list property to use a different range of cells so let's start with the UK rating system first of all I'm going to double click on that button to give me the click event of that button and then we can write a couple of simple lines of code in here to reset the values in the list okay so the first thing we're going to do is clear the contents of the original list and we do that really really simply by saying film certificates dot clear genuinely is as simple as out there clear method will clear all the contents of the original list plus any items you've currently got selected as well and in fact just to prove that let's switch back to excel briefly and let's launch the dialog box and if I select an item of the list at this point and then click my UK button it will delete the value that I've selected but also clear all the existing items in the list so it's worth noting that you can do that just independently by yourself anyway you can clear your list at any time by applying the clear method let's say close form down and then we will go back to the VB editor and continue with the code behind this button so the next thing I'm going to do is make sure that the the film certificate list is set to be equal to the UK ratings the BBFC ratings rather than using the the same technique I used earlier on or in fact let's just do this I'm going quick quickly click and drag to copy and paste this same code and just drop it straight in there okay so now that will reset the rating system for the UK racing the BBFC and what I'm going to do while I'm here is select the use us ratings button object from the drop-down list at the top left and that will generate the click event for that button I'm going to say film certificates or films a difficult clear and then we'll reset the film set of get list to be equal to the range of cells containing the MP double-a rating system so dot list equals sorry dot list equals rather than using the same relative selection technique I'm just going to refer to the range name to make things slightly easier I can refer to range MP double A ratings dot Val D okay so having done that switching back into Excel I should be able to launch my form and then I'll currently have the UK ratings that was the default entry that was selected if I click the US system you'll switch so now I can choose us ratings instead if I click UK you'll go back to the UK ratings and so on and so on and so on so let's get in simple idea that you can switch the values in your drop-down list using events of the form while the form is running okay so the last set of ideas to talk about with combo boxes is the idea of multi-column combo boxes so what I've done is I've added in an extra column into the main list that describes what who the film is suitable for and in the lists worksheet I've also added in a description column next to each M so next to each rating it's a rough description of who the film is suitable for so what that each rating is meant to have meant to imply there's not an accurate is just for demonstration purposes so I don't read too much into this and what I need to do or what I'm going to do is just quickly update the range names as well to make sure that they refer to to arrange themselves with multiple columns this should make my life easier when I'm starting to write code inside the form so to modify the definition of a range name you notice the formulas tab in the ribbon and choose the name manager and then in there you'll see a list of all the ratios you've created for the workbook if I select the BBFC ratings when I can choose to edit that and a couple of ways I can do it simplest is to click the cell selector button and just reset up the cells like so so that's the one for the BBFC ratings I'll do the same for the MP double-a ratings as well so I'll choose to edit that and then use a range selector to select that block of cells okay so those are the two great range names updated what we're going to do now is head back in to the VB editor and we're going to update our our drop-down list our combo box so it cannot accept multiple columns so the first thing I'm going to do here is choose what range of cells that dropdownlist populated with when the form first loads so that involves going to the initializing into the form so if I can go to the code view of the form and then I'm going to update the line which refers to what range of cells bliss is populated with to begin with I could do this in a relative using a relative cell selection technique so going from from cell a2 down to the end list downwards I could then simply extend that and say dot and Excel to write as well and then that refer to the values of all the cells from a to end downwards and to the right so that's the equivalent of if you were in Excel going from cell a2 holding down ctrl and shift and tapping the down arrow key and then the right arrow key so it'd be that block of cells it's a bit of overkill slightly unnecessary for this I'm going to assume got a fairly fixed list at this point so what I'm going to do instead of doing that is just refer to the BBFC ratings range I'm going to comment out those two lines and replace that with a film certificate dot list equals range BBFC of ratings dot value I'm going to copy and paste that line as well so I'm going to update our two buttons as well so I'm going to copy that and paste it into the use UK ratings subroutine and then comment out the two lines that use the relative cell selection techniques again and then a similar sort of thing in fact I don't need to change anything about the other US ratings because that uses the mpw ratings of range name already so I'm having done that what I'm going to do now is go back to excel and I'm going to show the form on screen by clicking one of my buttons on my menu sheet and although I have selected multiple columns for the range which populates a drop-down list I'm not going to see any extra values here the reason for that is because I haven't finished changing all the properties necessary to make combo box display multiple values so I'm going to close form down head back to the visual basic editor and then select the comma box and look at these other properties it makes it slightly easier to find all these properties by switching the properties list into categorized view and then you can scroll down through the list and find the data category so the reason we're only seeing one columns worth of data in our drop-down list is because the column count property is still set to one so let's simply update that little number two and then we can click on the background of the form and choose to run it and will suddenly see now that in the combo box we can see two columns of data so it probably makes a bit more sense maybe to make the common box a bit wider at this point we can also modify the widths of the columns in the in the properties window too so let's close the form and down and then I'm just going to make the actual form itself a little bit wider so let's change the form and then increase the width of the frame that I want to increase the width of all these objects all at the same time so let's just change all their widths but I can also as well as changing the actual physical width of the entire control I can change the individual column widths within the combo box so with the combo box selected again going back to the properties window and back to the data column I've got the column width property now the default units for the width serve the columns in a combo box are in points so if I just entered a number here like say 24 followed by a semicolon to move on to the next column and enter 24 again when I press ENTER those units will be entered as points we wanted centimeters instead so that's a maybe M I know 1.5 cm semicolon 1.5 cm when I hit enter those will be converted into the equivalent distance in points if I wanted to use inches I'd use I n instead right when we set those back to 24 and 24 and then see what effect that has when I run the form so I click on the background of the form and then run it I'll see the column that has now been set to the distance I've selected so yeah not too bad it kind of works I could really do with making the common box a bit wider again but I think I'm going to leave it at that so that's how you set the column widths of the combo box the next thing to look at is how to refer to the values of multiple columns in a combo box so we already have some code that's referring to the value of the combo box from the rehabs in earlier on if I double click the add to list button and then right click added a to the list and choose definition sitting right here is a line code that refers to the value of the film certificate to combo box so that will still work currently if I go back to excel and then just added in some random data some just some sample data doesn't matter what I type in here just some text a number a date and then choose a certificate from the list doesn't really matter which one when I click my add Tillis buttom we'll see that it returns the value of the first column the reason this happens is due to a property called the bound column if I switch back to the visual basic editor and select the combo box and scroll down to the data category again the bound column property set to the number 1 so this refers to the first column from left to right and specifies that it's the first columns value that gets entered into the value property of the control now if I want to refer to the value of another column in the combo box I've got a couple of different choices here one choice would be to use the text column property if I set that to the number two that means that the text property of my combo box now will refer to the value of the second column in the common box so what that means is I've now got a distinction a difference between the text property and the value property for the comm box previously those always would send the same value so I go back to my app - list button and then go back to my add data - list procedure what I could do here is say add to sell that offset 0 comma 3 don't value in fact let me just copy and paste this just for simplicity I can say here now film certificate dot text rather than dot value so because I've set the text column property to be equal to 2 that will return the value from the second column of the combo box I'm going to update this so I refer to the value of the cell that's offset for columns to the right now to avoid overwriting the certificate one more quick little test back into Excel I can then display the form on screen and type in some random numbers and use doesn't matter what as long as they're valid numbers and dates and then select an item from the drop-down list so if I click my atlas but now I will find indeed that I've got both the certificate and the description entered into the list now although changing the text column property does will have the desired effect in allowing us to refer to the value of a second column it does have a couple of slightly odd side effects one of which you may have noticed in the previous section if I just go back to excel and I choose to show my form on screen and then select an item from the drop-down list 12a kids with adults what will happen when I select this option is it doesn't show me the name of the certificates anymore it shows me the description of the certificate so it shows me the value of the text column that's one small issue not a major problem we could live with that and the other slightly weird thing that happens though is that the validation code doesn't quite work anymore if I show the form again and I try to add to the list without selecting an item you'll see that the validation code doesn't seem to be triggered early wrong we added in a statement that said if the value of the gormer box was equal to an empty string but when you've changed the text column property the value returned by an empty combo box isn't an empty string anymore yeah she returned the value null all the non value null thing that makes more sense and so what I'd have to do is change my validation code to check for nulls in the combo box or I could check the text property for an empty string rather than the value property so these changes are all or all surmountable but it probably makes more sense in this example to reset the text column property back to its original setting so that what we can do then is use a different method to refer to the values of different columns in the combo box so back in the visual basic editor I'm just going to select the combo box and change the text column property back to minus one that's setting by the way makes the text column refer to the value of the first column that does not have a zero width that's the technical description anyway and so just going back to excel this this means that the M the validation code will now still work if I click that's a list versus I think I think the validation code is triggered again and if I do select an item from the list it will select the or will display the value column so the the certificate name rather than the description the other issue we would have had with our text column property is it still only allows us to access one extra column in the combo box so in this case our column count is two so that that would have worked but if our column cam was more than that then the text column property doesn't really help so if you want to refer to any number two column in the entire combo box a matter how many columns you've got there's another property the news and that property is called column so I'm going to go back to the code behind the add to list button and then I'm going to view the definition of my add data to list procedure and then I'll scroll down a little bit and what we're going to do here is we're going to update the the section of code where we're referencing the value and the text of the film certificate so let's start by removing the value property and replacing that with a reference to the column property instead now the column property allows you to specify both the column index and the row index of the value you want to retrieve by open a set of parentheses the first thing have to do is specify the number of the column whose value I want to get now with the bound column and the text column properties the columns are indexed from a base of one so the first column is index number one second column is number two with a column property that's slightly different the column property uses a base of zero so the first column is indexed as zero and the second column is indexed as one and so on slightly confusing I know but that's just the way there is so you have to sadly accept it I can also optionally reference the row number of the item I want to retrieve now by default if I don't do that what will happen is that the column property returns the value from the specified column on the selected row so if I select for instance the PG row in the drop-down list it will use the route the details of the river the the PG films of BG Certificate sorry if I wanted to I could specify the the row index that I want to retrieve but to refer to the selected item is a little bit fiddly what I have to do is refer to the combo box itself first of all and then refer to the list index property so list index property refers to the index number of row of the selected item in the combo box there's a bit of a mouthful but say it's not actually necessary to do either you might well prefer in the real world just to omit that and just say column 0 to retrieve the value of the first column I'm going to leave it in there as I've made the effort to type it in just for reference and again we can then replace the the text property from the row bit further down again we can remove the text property altogether replace that with the reference for the column property and again this time we're referring to column number 1 so the second column confusingly and again if I wanted to I could refer to film certificates dot list index to refer to the selected item or the selected row again that's not necessary that's entirely your choice so having done all of that let's head back to excel give this one final test by showing the form on screen typing in some random text no matter again the number I don't care about and the release date just needs to be some kind of valid date and then the certificate let's select well let's use when we have enjoyed before let's choose number 18 grownups and then if I click my Atlas button I will find indeed that I get the 18 certificate and grownups added in so there we go that's pretty much everything there is to know about combo boxes for the time being the next video is going to talk about a slightly different form of Lists women talk about list boxes very very similar to a combo boxes work the main advantage of a list box is that it allows you to select multiple items whereas with a combo box you can only select one so hopefully you'll join us for that one and we'll see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials leaving some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 47,639

Rating: 4.8681316 out of 5

Keywords: vba, excel vba, forms, user forms, combobox, combo box, drop list, drop down, wise owl

Id: wgInu5NO9rY

Length: 41min 3sec (2463 seconds)

Published: Thu Mar 31 2016