The special effect used will be the 1– fmSpecialEffectRaised as shown below.ġ0) Now we will insert a combo box below the label. We will set the font to Georgia, the font style to bold, the font size to 12, and center align the text. If the Properties Window is not showing up, press the F4 key on your keyboard in order to see it.ĩ) We will now insert a label using the Toolbox (if you cannot see the Toolbox, for some reason go to View, Toolbox), change the Caption to Choose a student and we will change the BackColor to white in this case. It’s a good idea to superficially hide the back-end worksheets that contain the information, that you don’t want the user to edit or see.Ħ) Now with the UserForm sheet activated, we go to Developer>Code>Visual Basic in order to open the Visual Basic Editor (VBE).ħ) Once in the VBE interface, we go to Insert, UserForm as shown below.Ĩ) Using the Properties Window, we will rename our form to StudentLookup, change the Caption to Look up Student Information, change the BackColor to light blue and set the height to 300 px and the width to 350 px.
=INDEX(B2:B31, MATCH(“Diana Graham”, A2:A31, 0))ģ) Upon pressing CTRL-ENTER, we get the value of Females returned, as the gender as shown below.Ĥ) We will now name the range A2: A31, StudentNames as shown below.ĥ) Hide the StudentInformation sheet, by right-clicking and selecting Hide. Let’s remind ourselves quickly, if we wanted to use the INDEX and MATCH Functions in one formula, in the actual worksheet to give us the gender of the name of the student we want to look up. One is an empty sheet called UserForm, the other is a sheet called StudentInformation, which contains a range showing student names, their corresponding gender, and eye color as shown below.
#Vba code create combo box in excel userform how to
We are now going to see how to use the INDEX and MATCH Functions together in VBA code, in order to confer similar functionality to the look up UserForm we are going to create.ġ) We are starting off with two sheets in our macro-enabled workbook. We have already covered in detail, how to use INDEX and MATCH to perform advanced lookups in an Excel workbook as a straight worksheet formula, in a previous tutorial. The two in combination offer certain advantages over VLOOKUP. The INDEX and MATCH Functions are often used in combination in formulas, in order to perform advanced lookups.
Using the form, the user selects a name of the student, and then the corresponding gender of said student and eye color is retrieved and returned.įurther Readings Using INDEX and MATCH Worksheet Functions within VBA Code We are going to utilize the INDEX and MATCH Functions in our VBA code, in order to create a simple UserForm. So, let’s get started with an example showing how to utilize Excel Worksheet Functions in VBA code. All you basically need to do is access the function you need since it’s already there and there is then no need to reinvent the wheel. Also, you don’t have to come up with your own functions, unless you really need to, if the functionality is already there. Why would you want to use Excel Worksheet functions in your VBA code? Well to extend the functionality of the code you are using. Only a few worksheet functions are exempt from this rule, and this is in the case where there is already a VBA equivalent function.
In fact, most of the Excel worksheet functions can be accessed and used in VBA code. You can utilize the built-in Excel Worksheet functions such as the VLOOKUP Function, the CHOOSE Function and the PMT Function in your VBA code and applications as well.
0 kommentar(er)
