• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto change of validated cell when source in data validation changed

annieapplebee

New Member
Hello,

I have a sheet here that I use data validation on all columns in the master list tab, the list is generated in material color tab. How to automatically update the cell when the list change? I tried using macro, pasted this code in the master list worksheet but didn't work.

>>> use code - tags <<<
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strValidationList As String
Dim strVal As String
Dim lngNum As Long

On Error GoTo Nevermind
strValidationList = Mid(Target.Validation.Formula1, 2)
strVal = Target.Value
lngNum = Application.WorksheetFunction.Match(strVal, Range(strValidationList), 0)

If strVal <> "" And lngNum > 0 Then
    Application.EnableEvents = False
    Target.Formula = "=INDEX(" & strValidationList & ", " & lngNum & ")"
End If

Nevermind:
    Application.EnableEvents = True
   
End Sub
Your help is appreciated!

Thanks,
AnnieAppleBee
 

Attachments

  • Design Masterlist.xlsm
    129.8 KB · Views: 3
Last edited by a moderator:
Hello, according to any Excel forum rules - a must read before creating any thread ! - two issues with your thread :​
1) use the code icon in the 3 dots menu when posting any code;​
2) you forgot the link for each same thread created on another Excel forum(s) in order to avoid some wild cross posting …​
 
Back
Top