Hello,
I have a sheet here that I use data validation in the master list worksheet (Example, Column F chooses the fabric type, Column G selects the fabric color, which is dependent of F), the source list is in material color worksheet. How do I allow automatic cell update when the source in the list change? I tried this code in the master list worksheet in visual basics (applied the code to master list worksheet) but didn't work.
#
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
Bookmarks