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
I'm not a macro person, so if you insist on a macro/VBA answer, then feel free to ignore what I'm going to say.
When I read what you've written, that you're wanting to set a given cell to a value from (or derived from) a drop down (data validation) list, I wonder why you're using a macro in the first place. It sounds like something very amenable to a simple formula using one of the many Excel functions.
So would you be willing to back up and describe in normal vocabulary (i.e., not in Excel language) what it is that the data validation list contains, what you want to extract from it ... etc. What is the purpose being served?
Said another way, once we're clear on the end desired (the purpose being served), it's entirely possible that there is another cleaner way to get there. You've assumed that a macro is the way and are just looking to refine that method. I'm suggesting there may be a different way altogether.
Hello Mathetes,
Yes, this involves a macro.
For a better understanding I am uploading a self.explanatory file.
Hope someone can give a hand.Many thanks,
OctavioI'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- Thanks a lot, Hans, it works perfectly, as usually,
Octavio
Yes, this involves a macro.
I know it does involve a macro. My question was whether a macro is necessary.
You haven't responded to that question with an explanation of the purpose being served by a macro that can't be achieved any other way. Just so you know, Macros are often invoked by folks who aren't aware of the power of some of Excel's functions. I have no doubt that a macro can work; but there often are cleaner solutions that do not involve macros.
I'll take your response, though, as an indication of no interest on your part in any other possible solution and bow out of the conversation.
Do you know a solution for this not using vba? If you do please let me know which is it. If such solution existed do you think I will spend my time re-inventing the wheel...
