Excel VBA-选择数据验证下拉列表中的第一项

我正在尝试让一个单元格自动将其第一个值添加到该单元格的数据验证下拉列表中。还没能在网上找到任何有用的东西。

当用户输入工作簿其他部分的信息时,此Sub将由专用Sub激活。

我也不能列出这个下拉列表将从中选择的范围,因为这将是动态的。我见过多个使用“with”语句的例子,但它们似乎需要这个列表的工作范围。

我应该提到,单元格“LastProject”已经包含了一个下拉列表。

有人有更好的主意吗?

Dim LastProject As Range
    Set LastProject = FoundBMR.Offset(0, 1)

'''Find Last Project Produced for unit
    ''' Function ListSourceRange Required.
    Dim rngSource As Range
        
    rngSource = LastProject
        
    Set rngSource = ListSourceRange(Target)
        If Not rngSource Is Nothing Then
            rngSource.Parent.Activate
        End If
    
    With LastProject
        .ClearContents
        .Validation.Delete
        .Validation.Add Type:=xlValidateList, Formula1:="+" & rngSource
        .Value = rngSource.Cells(1, 1).Value
    End With

Function ListSourceRange(c As Range) As Range
    Dim vType, rng As Range
    On Error Resume Next       'ignore error if no validation
    vType = c.Validation.Type
    On Error GoTo 0            'stop ignoring errors
    
    If vType = 3 Then
        'try to get a source range...
        On Error Resume Next
        Set rng = Range(c.Validation.Formula1)
        On Error GoTo 0
    End If
    Set ListSourceRange = rng 'source range, or Nothing if no range found
End Function