Last week we learned how to create dynamic hyperlinks in Excel. Today, I want to show you something even cooler. An interactive dashboard based on hyperlinks. An index with links to all Microsoft Word Help, Tips and Tuitorials The Anchorage. Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work. Using Excel Worksheet Functions INDEX MATCH 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. In fact, most of the Excel worksheet functions can be accessed and used in VBA code. Only a few worksheet functions are exempt from this rule, and this is in the case where there is already a VBA equivalent function. Why would you want to use Excel Worksheet functions in your VBA code Well to extend the functionality of the code you are using. Also, you dont have to come up with your own functions, unless you really need to, if the functionality is already there. I would like to use a RichTextBox control in a Form in my VBA application for Word. I wish to know if it is possible to add a RichTextBox Control to Office. Office 365 client update channel releases. Get information about all of the channel releases for Office 365 client applications. Check this page regularly for the. Access Vba Add Microsoft Rich Textbox Control Download' title='Access Vba Add Microsoft Rich Textbox Control Download' />All you basically need to do is access the function you need since its already there and there is then no need to reinvent the wheel. So, lets get started with an example showing how to utilize Excel Worksheet Functions in VBA code. We are going to utilize the INDEX and MATCH Functions in our VBA code, in order to create a simple User. Form. 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. Using INDEX and MATCH Worksheet Functions within VBA Code. The INDEX and MATCH Functions are often used in combination in formulas, in order to perform advanced lookups. The two in combination offer certain advantages over VLOOKUP. 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. 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 User. Form we are going to create. Read More Index Function Excel Examples, Make Dynamic Range, INDEX MATCH1 We are starting off with two sheets in our macro enabled workbook. One is an empty sheet called User. Form, the other is a sheet called Student. Information, which contains a range showing student names, their corresponding gender, and eye color as shown below. Lets 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. We would use the following formula INDEXB2 B3. MATCHDiana Graham, A2 A3. Upon pressing CTRL ENTER, we get the value of Female returned, as the gender as shown below. We will now name the range A2 A3. Student. Names as shown below. Hide the Student. Information sheet, by right clicking and selecting Hide. Its a good to idea to superficially hide the back end worksheets that contains the information, that you dont want the user to edit or see. Now with the User. Form 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, User. Form as shown below. Using the Properties Window, we will rename our form to Student. Lookup, change the Caption to Look up Student Information, change the Back. Color to light blue and set the height to 3. 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 Back. Final Fantasy Vii Crack Remake Of Stephen. Color to white in this case. We will set the font to Georgia, the font style to bold, the font size to 1. The special effect used will be the 1 fm. Special. Effect. Raised as shown below. Now we will insert a combo box below the label. Name this combo box cmd. Student. Name and for the Row. Source, type Student. Names this is the named ranged containing the student names that we named in the actual worksheet. In order to see the effect of setting the Row. Source of the combo box, click the Run SubUser. Form button as shown. Now because of setting the Row. Source to the named range, when the user clicks on the drop down arrow on the User. Form, the combo box is now populated with the student names from the named range, automatically as shown below. Close the User. Form by clicking on the close button. Press Alt F1. 1 in order to go back to the VBE. Read More VLOOKUP versus INDEX and MATCH versus DGET1. Once back in the VBE, add another label to the User. Form below the combo box and change the Caption to Gender and we will change the Back. Color to white in this case. We will set the font to Georgia, the font style to bold, the font size to 1. The special effect used will be the 1 fm. Special. Effect. Raised as shown below. Create a textbox below the Gender label, and name it txt. Gender, as shown below. Add another label called Eye Colour and a textbox named txt. Eye. Colour as shown below. Use the same properties for the label as for the two other labels previously added to the form, in order to ensure that the User. Form has a consistent look. Now select all the controls, added to the User. Form, thus far using the control key. Center horizontally, as shown below. Next, add a button to the form using the Toolbox. Change the Name of the button to cmd. Look. Up, the Back. Color to light orange, keep the Tahoma font and change the style to bold, finally change the Caption of the button to Look up Student Details as shown below. Right click, the newly added button, and select View Code. Enter the following code for the button click event Dim a As. Variant. Dim b As. Variant. Dim c As. Variant. a cmd. Student. Name. Value. SheetsStudent. Information. Activate. Let txt. Gender. Text b. Let txt. Eye. Colour. Text c. b Application. Worksheet. Function. IndexSheetsStudent. Information. RangeB2 B3. Application. Worksheet. Function. Matcha, SheetsStudent. Information. RangeA2 A3. Let txt. Gender. Text b. Application. Worksheet. Function. IndexSheetsStudent. Information. RangeC2 C3. Application. Worksheet. Function. Matcha, SheetsStudent. Information. RangeA2 A3. Let txt. Eye. Colour. Text c. End. If. We start off by declaring three variables and assigning the variant data type to these declared variant data types. The variant data type is a good data type to get started with, since when working with worksheet functions, you may not always be sure of the outputs, therefore use the variant data type, when you are starting out. Later on, as you become more experienced with VBA and the different data types, it is advisable to use one of the other more specific data types such as integer or string, since, for more advanced longer code, the variant data type does not use memory as efficiently as the other data types. Variable as value is drawn from the option the user selects in the drop down combo box on the User. Form. If there is no selection, then all the other textboxes are empty. If a student name is selected from the combo box on the User. Form then variable bs value is attained by using the INDEX Worksheet Function in combination with the MATCH Function in the VBA code, as shown. Read More How to Create a Body Mass Index BMI Calculator in Excel Using VBAIt looks up the value using basically the same syntax as the worksheet function. When using worksheet functions in VBA, the VBA Intelli. Sense in this particular case is not very intuitive, therefore a familiarity with the syntax gleaned from worksheet knowledge is recommended. Variable cs value is also attained by using the INDEX Worksheet Function in combination with the MATCH Function in the VBA code when the user selects an option from the combo box. Variable bs value is sourced from the gender column in the worksheet, whereas variable cs value is sourced from the Eye colour column in the worksheet. The gender textbox is populated with bs value and the eye color textbox is populated with cs value. Now go to the worksheet called User. Form in your workbook. Format it, as shown below and insert the image provided by Excel.