Set cell value to item value of data validation list

[oteixeira62]

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
                .Validation.Delete
                .Validation.Add Type:=xlValidateList, Formula1:="=CTRYS_MOV_ANO"
            End With
        .ClearContents
        End With
    End If
End Sub

How can I add the lines to set cell E4 value to the first(or other) value of the "CTRYS_MOV_ANO list?

Many thanks,

Octavio

[#2]

@oteixeira62 

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.

[#3]

@mathetes 

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

[#4]

@oteixeira62 

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.

[#5 · Accepted solution]

@oteixeira62 

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

[#6]

@mathetes 

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...

[#7]

Hans, cannot test now but I will ill let You know. Many thanks for time and patience. Octavio

[#8]

@oteixeira62 

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. 

[#9]

@mathetes 

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

[#10]

Thanks a lot, Hans, it works perfectly, as usually,
Octavio

[#11]

@oteixeira62 

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.

[#12]

Thank You