Forum Discussion
Solved
Set cell value to item value of data validation list
Hello to all, I have this piece of code: Public Sub mudaval()
If Range("K2").Value = "Por mercado" Then
Range("E4").Select
With Range("E4")
With Selection
...
I'd do it like this:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("K2")) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False With Range("E4") .Validation.Delete If Range("K2").Value = "Por mercado" Then .Validation.Add Type:=xlValidateList, Formula1:="=CTRYS_MOV_ANO" .Value = Range("CTRYS_MOV_ANO")(1).Value Else .ClearContents End If End With Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
From my point of view, every time the type of validation changes in a cell, it should be updated and show the available values, but perhaps this is not technically possible.
This may not be directly relevant to your situation, but given that last sentence, you might find the attached interesting, in that secondary and tertiary data validation lists DO change based on what's entered in the first. And you can add more primary, secondary and tertiary values to the basic table and see the choices changed, the range of choices changed. No macros or VBA involved.
Thank You