Welcome to Excel Jet Consult blog post. In this tutorial, we learn about looking up values in Excel VBA Userform text boxes using the new XLOOKUP Function.
Video
The video of this blog post is available to watch and share
Sample Data
In the caption below, we have sample data that spans from columns A to H. This is a fictitious Sales Records we need to retrieve (look up) inside the Sales Management System userform seen below
In the userform, we have Order ID as combo box while Date all through to Total are text boxes. We also have Retrieve Records and Clear Records as command buttons respectively.
Create Named Range
The first thing we will do is to create named range based on Order ID in column A. We need the named range as an input value for the row source located in the properties of the Order ID combo box.
Therefore, to create named range.
- Select cell A1 and deploy CTRL + SHIFT + Down Arrow. In addition, execute CTRL + Backspace to move up
- In the Defined Names group of the Formulas tab, click on Create From Selection or deploy CTRL + SHIFT + F3
- In the Create From Selection dialogue box, only check Top Row as seen below
- Click OK
Open Visual Basic
Next, we need to open the VBA editor where we will write our codes
- In the Code group of the Developer tab, click on Visual Basic located left on the screen.
Note, Sales Management System userform has already been build without codes. To locate the userform,
- Under Forms, click on Records. See the caption below
- Click on the Order ID combo box
- In the Properties ComboBoxID, locate Row Source (bottom left of the caption below)
- Inside the Row Source, type in the named range accurately which, in this example is, Order_ID
- Click Enter on the keyboard
XLOOKUP VBA Parameters
The XLOOKUP Function searches a range or an array for a match and returns the corresponding item from a second range or array.
XLOOKUP Function Syntax in standard excel is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
However, the XLOOKUP Function has the following parameters
Application.WorksheetFunction.XLookup(Arg1, Arg2, Arg3, [Arg4], [Arg5], [Arg6]).
Command Button Code
Next, we write the code by clicking on the Retrieve Records command button
- Double click on Retrieve Records command button
- In the code window, execute the XLOOKUP function VBA Code as seen in the caption below
Private Sub CommandRetrieveRecords_Click()
Me.txtDate.Value = Application.WorksheetFunction.XLookup(Me.ComboBoxOrderID.Value, Sheets(“Sales Records”).Range(“A2:A101”), _
Sheets(“Sales Records”).Range(“B2:B101”))
Me.txtCustomerName.Value = Application.WorksheetFunction.XLookup(Me.ComboBoxOrderID.Value, Sheets(“Sales Records”).Range(“A2:A101”), _
Sheets(“Sales Records”).Range(“C2:C101”))
Me.txtProducts.Value = Application.WorksheetFunction.XLookup(Me.ComboBoxOrderID.Value, Sheets(“Sales Records”).Range(“A2:A101”), _
Sheets(“Sales Records”).Range(“D2:D101”))
Me.txtUnitSold.Value = Application.WorksheetFunction.XLookup(Me.ComboBoxOrderID.Value, Sheets(“Sales Records”).Range(“A2:A101”), _
Sheets(“Sales Records”).Range(“E2:E101”))
Me.txtPrice.Value = Application.WorksheetFunction.XLookup(Me.ComboBoxOrderID.Value, Sheets(“Sales Records”).Range(“A2:A101”), _
Sheets(“Sales Records”).Range(“F2:F101”))
Me.txtShippingCost.Value = Application.WorksheetFunction.XLookup(Me.ComboBoxOrderID.Value, Sheets(“Sales Records”).Range(“A2:A101”), _
Sheets(“Sales Records”).Range(“G2:G101”))
Me.txtTotal.Value = Application.WorksheetFunction.XLookup(Me.ComboBoxOrderID.Value, Sheets(“Sales Records”).Range(“A2:A101”), _
Sheets(“Sales Records”).Range(“H2:H101”))
End Sub
Run Code
Next, we need to put our code to work! To run the code,
- Click on the Play Button to run the code
In the caption below, I selected SCM1004 Order ID
When I click the Retrieve Records command button, all the records for corresponding came up in their respective text boxes as seen below
In the caption below, the retrieved record tallied with what is available on the spreadsheet highlighted in yellow fill color
Conclusion
We have seen how to use the XLOOKUP Function with the combination of named range, combo box, text boxes and row source to look up values in text boxes based on combo box selection. See you in the next tutorial. Remember to watch the video and subscribe to the YouTube channel