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,
Octavio
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- Thanks a lot, Hans, it works perfectly, as usually,
Octavio - Hans, cannot test now but I will ill let You know. Many thanks for time and patience. 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...
You asked: Do you know a solution for this not using vba?
I can't offer a solution without first knowing what it is your macro is designed to achieve.
This is what I wrote in my first post:
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.
If such solution existed do you think I will spend my time re-inventing the wheel...
Not if you knew what that solution is; of course not. But I definitely have known that to happen, and more than once, often with people who are adept at programming. They just assume that a VBA would work, they understand how it can work, how it can be written, and so will write a routine that works and are happy with it. As I say, especially people with a background in programming. Here's a true story that illustrates the point:
Years ago (decades ago, actually), I had responsibility for creating a monthly headcount report for a major US corporation. I was the director of the HR/Payroll database for the company, and that monthly report was part of my group's responsibility. What I learned was that the people in the IT department (the group that supported us) had written a very elaborate program to extract data from the (mainframe) database each month and break it down by all the necessary categories. It took something like 30 minutes for that program to run. It did work; the report created was reliable. But my sense was that it could be done more quickly just in a native spreadsheet way. I created an SQL query to extract the data necessary and export it to (at the time) Lotus 1-2-3. And then used native spreadsheet functions to produce the report in minutes, start to finish. The IT folks had the tool they were comfortable with and used it; so never bothered to investigate what the spreadsheet could do. Does that happen still? Of course.
I don't by any means know that this is your situation. Nor am I accusing you of doing that. All I said at the very start was that your description (although I'd like to have you describe it more fully) made it sound like something that native Excel functions might handle relatively easily. But it only sounded like it; it wasn't clear to me what the VBA routine is doing, and without that clarity, I don't know for sure.