VBA to select first item from drop down list which references a named range

[husker5]

I have a sheet which lists part numbers in column A and has a drop down list in column B which specifically corresponds to the part number in the adjacent cell. The drop down list references a name range "MyFinish" which has the following code "=OFFSET(MyFinishList,0,0,COUNTA(MyFinishList),1)"
"MyFinishList" is another namerange with the code "=INDEX(Table1,0,MATCH(Lozier!A13,Table1[#Headers],0))"
"myCategory" refers to the "Table1" Headers and has finish codes listed vertically under the "#Headers" which is each part number listed in Column A.

This allows the drop down to only list the available colors for the part listed in the adjacent cell.
If I change the part however, the displayed drop down value shows the previous item until it is manually clicked in which the list displays the updated items corresponding to the new part.

I would like to know if there is a way through VBA to automatically display the 1st item in the drop down list so that they are automatically updated anytime the part list in column A is updated.
I can perform this action if the data validation is referencing a range but have not figured out how to reference a name range.

I have uploaded a sample of my workbook along with my current code to simply update cell B2 to the 1st item.
https://www.dropbox.com/s/c5binlryifqlo56/Test.xlsm?dl=0

Sub ChangeFinish()
Range("B2").Value = Range(Replace(Range("B2").Validation.Formula1, "=", "")).Cells(1, 1).Value
End Sub

Any help would be greatly appreciated.

 

[AlphaFrog]

• Right-click on the List sheet tab
• Select View Code from the pop-up context menu
• Paste the code from below in the worksheet's code module

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range, rng [color=darkblue]As[/color] Range
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("A:A"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Intersect(Range("A:A"), Target)
            [color=darkblue]Set[/color] rng = Sheets("Table").Rows(1).Find(cell.Value, , , xlWhole, , , 0)
            [color=darkblue]If[/color] [color=darkblue]Not[/color] rng [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
                Application.EnableEvents = [color=darkblue]False[/color]
                cell.Offset(, 1).Value = rng.Offset(1).Value
                Application.EnableEvents = [color=darkblue]True[/color]
                [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] cell
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[husker5]

Thank you for the quick reply. However, how do you activate this code and what is "Target" defined as?