Forum Discussion

oteixeira62's avatar
oteixeira62
Copper Contributor
Feb 06, 2023

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

Share