Home > Validation > Drop Downs > Macro

Drop Down Change Product Name to Code

Select a product name from a data validation drop down list, and a macro automatically changes the product name to a product code. See how to set this up, and get the sample file for testing.

Change Product Name to Code

To help you select the correct product when ordering, this data validation drop down list shows the product name, followed by its product code.

However, the order department only needs the product code, so a macro automatically updates the cell, after you've selected an item. Instead of showing all the product info, only the code is left in the cell.

This video shows the steps for setting up the drop down list, and you can get the sample file in the download section below. The full transcript for the video is at the end of the page.

Product List

On the Codes sheet, there is a list with 3 columns:

1. ID#:  Typed product codes
2. ProductName:     Typed product names
3. ProductShow:    Formula combines product name and code, using the & (ampersand) operator.

Here is the formula in cell C2: = B2 & " -- " & A2

NOTE: The ProductShow column will be used in the drop down list, so people can see both the product name and code.

Create an Excel Table

The list was formatted as a named Excel table, by following these steps:

• Select any cell in the product list
• On the Ribbon at the top of Excel, go to the Insert tab
• Click Table
• The product list range should be automatically selected.
• The table has headers, so click OK

TIP: Sort the list by Product name, so products will be easy to find in the drop down list.

Create a Named Range

Next, I created a named range based on the ProductShow column, by following these steps.

• Click at the top of the ProductShow column, to select the data, but not the heading.
• On the Excel Ribbon, click the Formulas tab
• Click Define Name, and enter a one-word name, ProdList
• The reference should be automatically filled in, showing the table name (Table4) and the column name (ProductShow).
• Click OK, to complete the name.

NOTE: Because it's based on a formatted table, the ProdList named range is dynamic -- it will automatically grow or shrink, if you add or remove products.

Drop Down List Setup

On the DataEntry sheet, there are drop down lists in the Product column. Here are the steps to set those up:

• Select all the cells where the product drop downs are needed.
• On the Data tab, click Data Validation.
• From the Allow drop down, choose List
• Click in the Source box, and press the F3 key on the keyboard, to show the Paste Name dialog box
• Click on the ProdList name, then click OK
• Click OK, to apply the data validation

Now we have our drop-down list, showing the descriptive name and the code.

Test the Drop Down

To test the drop down list:

• Select an item from the drop down list
• The cell value will change automatically, to just show the product code number.

See the Worksheet Code

There's code on DataEntry sheet's code module, that runs automatically, if you enter a value in column B (column #2)

The code is shown in the next section, and to see the code in the sample file:

• Right-click the DataEntry sheet tab
• Click View Code

Here are the steps that the code goes through:

• Check if more than one cell is selected
  • If so, exit the macro
• Check if
  • the change was made in column 2
  • AND the changed cell is not empty
  • If either is not true, exit the macro
• In the ProdList range, use the MATCH function to find its row number in that range
• Get the Product Code number from that row
• Put that Product Code number in the changed cell on the DataEntry sheet

Drop Down List Macro Code

Here is the Excel VBA code that is on the DataEntry sheet's code module.

Private Sub Worksheet_Change _
  (ByVal Target As Range)
' www.contextures.com
On Error GoTo errHandler

Dim wsCodes As Worksheet
Set wsCodes = Worksheets("Codes")

If Target.Cells.Count > 1 _
  Then GoTo exitHandler

If Target.Column = 2 Then
  If Target.Value = "" _
      Then GoTo exitHandler
  Application.EnableEvents = False
  Target.Value = wsCodes.Range("A1") _
    .Offset(Application. _
        WorksheetFunction _
        .Match(Target.Value, _
        wsCodes.Range("ProdList"), 0), 0)
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

errHandler:
  If Err.Number = 13 _
    Or Err.Number = 1004 Then
    GoTo exitHandler
  Else
    Resume Next
  End If

End Sub

Get the Sample File

1. Download the completed Change Product Name to Code sample file, to see how this technique works. The zipped file is in xlsm format, and contains macro. Be sure to enable macros if you want to test the drop down list event code.
2. Download the older sample file that was used in the video. The data entry sheet does not have a named Excel table in this version. The zipped file is in xlsm format, and contains macro. Be sure to enable macros if you want to test the drop down list event code.

Video Transcript

Here is the full transcript for the video at the top of this page.

Drop Down List Changes Product Name to Code

On this worksheet we want users to be able to select a product from a drop-down list and then enter a quantity.

On another sheet, we have the product list. @e have codes, and then a descriptive name. In another column, we've used the ampersand operator to combine the product name and the code.

We'd like to use this full description in the drop-down list, so people can see the name that will mean more to them than the code, but we'll also see the code.

When the list is entered, we're going to have to send this to the accounting department, and they're only interested in the ID number so they can get all the pricing information.

So on this sheet, after something's selected here, we're going to run some code that will automatically change this full description into the matching ID number.

The first step is to create a table from this range of cells.

• Selecting any cell in that list, on the Ribbon go to Insert > Table, and this range should be selected.
• We have headers, click OK, and there's our table.

The next step is to create a named range based on this ProductShow column.

• I'm just going to click at the top of this list, and that selects just the items, but not the heading.
• To create the name, I'll go to the Formulas tab, click Define Name and I'll call this ProdList
• The reference is already filled in. It's showing Table4 and the ProductShow column.
• Click OK.

This list is dynamic -- it will grow or shrink, as we add or remove products.

On the DataEntry sheet, I'll select all the cells where I want the drop downs.

• On the Data tab, click Data Validation.
• Allow: List
• Press the F3 key on the keyboard, click the name
• Click OK, and OK

Now we have our drop-down list, showing the descriptive name and the code.

There's code on this worksheet, if you right-click, and click View Code, here's what's happening.

• It's checking just to make sure we haven't changed more than one cell, and that we're in column 2 and the cell that we just changed isn't empty.
• Then it will look for the value we selected, so the Target.Value.
• Going onto the Code sheet, starting in cell A1, it's going to move down that list, to find the right code.
• It will find the right row, based on the value we selected, matching that in our product list. If we selected the second item, it's going to go down 2 rows.

To see how it works, we'll select bookshelf, and the code should become 89307.

As soon as I click on that, the code runs and it changes that full description into just the product code.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

Data Validation Basics

Data Validation Tips

Data Validation With Combo Box

Named Excel Table

Named Range

MATCH Function