All right, I've got kind of a more advanced example here, of how we can use combo boxes in user forms. What we're going to be creating in this screencast. We have all the elements here, we have their symbol, their atomic number, the atomic mass. And we want to make a user form, such that when we run this, it's like an element finder. So if I select from the drop down list, I can select an element. I can click GO and it gives me the symbol, atomic number, and atomic mass. And I can use this for all sorts of different elements. So that's what we're going to be creating in this screencast. On the course website, I have a starter file, it's called Periodic_Table-STARTER.xlsm. And you can go ahead and download that and work along with me. You should feel comfortable with being able to add these different elements here. I've again done this just to save time. But I've got a QUIT button, I've got an output field called Atomic mass. This is named Atomic number, this text box is named Symbol, I've got a GO button. And then I have added here, this is a combo box that I've added from down here. So let's go ahead and put in the code here. Quick button is the easy thing to do. We can just unload UserForm1, the name of our user form is UserForm1. Let's go ahead and work on populating this combo box. Remember when you're using combo boxes, before you open up the user form in a module, you need to populate the combo box. So I'm going to go ahead and insert a module, and I've named it something like PopulateComboBox. What we're going to do here on the spreadsheet is we're going to count the number of elements in A. And just keep in mind that the first row here, these are labels. We're going to start with two when we actually populate our list. We're going to be populating our combo box using the elements that are in column A. And, for reasons that I will explain in a few moments, I'm going to declare n as a public variable and that's going to be an integer. That way, n can be shared with the other subroutines that are used in the user form. We can use the CountA function in Excel to count the items in column A. I forgot to Dim i as an integer, that's going to be an index of iteration. And then we're going to bring in each element of column A. And we're going to make that an item in our combo box. So for each item in column A, I can use UserForm1.ComboBox1.AddItem Range("A" & i). i is going from 2 to n, because the first row is composed of labels. So we do that, we bring in all 114 elements in there into the combo box as new items. And then we want to make sure, at the end, that the default text is just the first item, which is hydrogen and that's in range A2. I'm going to make a RunForm Sub that's going to populate the combo boxes. And then it's going to open up or show UserForm1. So let's go ahead and see if this works. I'm going to press F5, that brings up our Element Finder. And we see that in our drop down menu, we're going from hydrogen, that's the first element, all the way down to this last one. So right now, we've got this to work, we've populated the combo box. But now we need to code what goes behind the GO button. When we click GO we want to extract the symbol, atomic number and atomic mass of that particular element. Now when we press GO we're going to use the list index of the ComboBox1 as sort of our index that we can use on the spreadsheet to obtain the symbol, atomic number, and atomic mass. So I can double click on the GO button, and this is where the code is going to go for when the GO button is clicked. The first thing we want to do is obtain an index, and that's the list index from that combo box. So I'm Dimming index as integer. And then, we obtain index using UserForm1.ComboBox1.ListIndex. So that's the list index that is returned when we press the button that we had selected in that combo box. Now, remember the list index starts with zero. So the first element has zero, the second element has one, and so on. So we have to remember that. The approach here then, once we know the index. For example, Boron would have an index of- well, we're starting at hydrogen so hydrogen is zero. One, two, three, four, so boron has an index of four. And we're going to use that then, we're going to use that in a range here, and we're going to take the index plus one row. And we're going to then take the second column, third column, or fourth column, depending upon if we want to output the symbol, atomic number, or atomic mass. So I'm going to write, for the first thing here, symbol. Remember symbol on our user form is just this text box here, so that’s what symbol is. So I’m going to write symbol equals, and I’ve written Range (“A2:D” and we’re going to concatenate that with n. Remember n in our module, we made n a public integer and n is the count. We can use that in this Private Sub because we've made it public. So we're looking at range A2 to Dn, and n is, I don't know, 114, 115. That's going to be kind of our base object, range A2 to D115, or so. Then we're going to take cells of that, so really we're only starting with the second row. But then we have to take our index + 1, because index starts at 0. And then we're taking the column of that range. Symbol is in the second column. I'm going to do the same type of thing for atomic number and atomic mass. The only difference here now for atomic number, we need column 3 for atomic mass, this should be mass. We need to export, or extract, the fourth column of this range. So I think that we are all set up to go. I'm going to put a button on the sheet. We can assign that button to the RunForm subroutine. And then when we click GO it brings up our Element Finder. Let's just make sure hydrogen works. So we extract then the symbol, the atomic number, and the atomic mass. And let’s do it for another element, and when we do that we obtain the properties of that element. So looks like it’s working just fine. Now I wanted to show you one more advanced feature. Instead of clicking the GO button every time you wanted to output the symbol when you change the element in this drop down menu. You can double-click on the code for the GoButton, and I'm just going to copy everything inside here. I'm going to go back to the user form, and I'm going to click on the combo box. This is where, whenever something is changed in that combo box, ComboBox1_Change, It's going to reevaluate what's inside of here. So now when I run my user form, if I reselect a item from the combo box, it's going to rerun all the code in that Change subroutine. And it automatically updates things so you don't need to press the GO button. This kind of shows you another example of using drop down menus in user forms. Thanks for watching.
