Forum Discussion

oteixeira62's avatar
oteixeira62
Copper Contributor
Feb 06, 2023
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
                .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

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Feb 06, 2023

    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
  • mathetes's avatar
    mathetes
    Silver Contributor
    Feb 06, 2023

    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.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 06, 2023

        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

Resources