Forum Discussion
Set cell value to item value of data validation list
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
Hello,
So would you be willing to back up and describe in normal vocabulary (i.e., not in Excel language)
Let's assume that users know that they have to open a container to choose an object from there. As there are several containers and they are all closed they won't know which one to open to perform their task. Opening the container and showing what's in it avoids questions and speeds up the process.
You have to see this situation from the point of view of the user with little experience and knowledge of Excel. They know how to press buttons and other objects and expect everything to work perfectly without having to perform extra tasks. 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.
Thanks,
Octavio
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