基于另一个工作表的列表框vlookup代码

Sub CreateDropdownList()
    Dim wsData As Worksheet
    Dim wsDropdown As Worksheet
    Dim rngData As Range
    Dim rngDropdown As Range
    Dim lastRow As Long
    
    ' 设置工作表对象
    Set wsData = ThisWorkbook.Sheets("DataSheet") ' 数据工作表
    Set wsDropdown = ThisWorkbook.Sheets("DropdownSheet") ' 下拉列表框工作表
    
    ' 获取数据范围
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    Set rngData = wsData.Range("A2:B" & lastRow) ' 假设数据范围为A2:Bn
    
    ' 清除下拉列表框数据
    wsDropdown.Range("A2:A" & wsDropdown.Cells(wsDropdown.Rows.Count, "A").End(xlUp).Row).ClearContents
    
    ' 创建下拉列表框
    Set rngDropdown = wsDropdown.Range("A2") ' 假设下拉列表框起始单元格为A2
    With rngDropdown.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='" & wsData.Name & "'!$A$2:$A$" & lastRow
    End With
    
    ' 设置下拉列表框单元格格式
    rngDropdown.NumberFormat = "General"
    
    ' 监听下拉列表框变化事件
    wsDropdown.Shapes("Drop Down 1").ControlFormat.ListFillRange = rngDropdown.Address
    wsDropdown.Shapes("Drop Down 1").OnAction = "DropdownChange"
End Sub

Sub DropdownChange()
    Dim wsData As Worksheet
    Dim wsDropdown As Worksheet
    Dim rngData As Range
    Dim rngDropdown As Range
    Dim selectedValue As String
    Dim result As Variant
    
    ' 设置工作表对象
    Set wsData = ThisWorkbook.Sheets("DataSheet") ' 数据工作表
    Set wsDropdown = ThisWorkbook.Sheets("DropdownSheet") ' 下拉列表框工作表
    
    ' 获取数据范围
    Set rngData = wsData.Range("A2:B" & wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row) ' 假设数据范围为A2:Bn
    
    ' 获取选择的值
    selectedValue = wsDropdown.Range("A2").Value
    
    ' 使用VLOOKUP函数检索相关数据
    result = Application.VLookup(selectedValue, rngData, 2, False)
    
    ' 将结果显示在指定单元格
    wsDropdown.Range("B2").Value = result
End Sub