Set an Excel Data-Validation Cell to the First List Value with VBA
Question details
Set a data-validation cell to the first value in a named range when a controlling cell changes.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A named range supplies a data-validation list, and a worksheet change event should select its first item when the controlling cell has a specified value.
- Observed behavior
- When the controlling cell changes to the specified value, the validated cell should receive the first item from the named range.
In Excel, use Formulas > Name Manager to confirm that CTRYS_MOV_ANO refers to the intended range on the worksheet and that its first cell contains the desired default. Adjust the example cell addresses, named range, and controlling value to match your workbook.
Use a Worksheet Change Event to Set the First List Value
Use this macro when a change to a controlling cell should update the validation list and assign its first value automatically.
In this example, K2 is the controlling cell, E4 is the validated cell, and CTRYS_MOV_ANO is the named range. When K2 equals "Por mercado", the macro applies the list and assigns the range's first cell to E4.
For any other value in K2, the macro removes E4's validation and clears its contents. The event runs when K2 is changed directly; it does not run merely because a formula in K2 recalculates.
Right-click the worksheet tab in Excel and select View Code. Paste the macro into that worksheet's code window, not a standard module.
Paste this code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("K2")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Me.Range("E4") .Validation.Delete If .Parent.Range("K2").Value = "Por mercado" Then .Validation.Add Type:=xlValidateList, Formula1:="=CTRYS_MOV_ANO" .Value = Me.Range("CTRYS_MOV_ANO").Cells(1, 1).Value Else .ClearContents End If End With CleanUp: Application.EnableEvents = True End Sub
Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). When reopening the file, enable macros if you trust the workbook.
On the worksheet, enter Por mercado in K2. Check that E4 displays the first value in CTRYS_MOV_ANO and offers the named range as its drop-down list.

Work with Spreadsheet Lists in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing Excel spreadsheets, making it easy to move everyday spreadsheet work across. The Excel VBA event macro above may not run in WPS Office; check macro availability and compatibility before migrating a macro-dependent workbook.

Frequently Asked Questions
Where should I put the Worksheet_Change code?
Right-click the worksheet tab containing K2 and E4, select View Code, and paste the procedure into that worksheet's code window.
Why does E4 become blank when K2 has another value?
The Else branch runs .ClearContents after deleting E4's validation. Modify that branch if you want a different default or need to retain the drop-down.
Will the macro run when a formula in K2 recalculates?
No. Worksheet_Change does not fire solely because a formula result changes during recalculation. A different approach, such as a Worksheet_Calculate event with additional change tracking, is needed.
Why must I save the workbook as XLSM?
The Excel Macro-Enabled Workbook format preserves VBA code. Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm), then enable macros when prompted for a trusted file.




