Forum Discussion
oteixeira62
Feb 06, 2023Copper Contributor
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
...
- Feb 06, 2023
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
HansVogelaar
Feb 06, 2023MVP
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
oteixeira62
Feb 07, 2023Copper Contributor
Thanks a lot, Hans, it works perfectly, as usually,
Octavio
Octavio