java excel 动态下拉列表框,如何在Excel中创建动态下拉列表

假设您的数据位于列A：B中，其中row1是 Headers 行，并且您有一个名为 Days 的命名范围，则右键单击工作表选项卡 - >查看代码并将下面给出的代码粘贴到打开的代码窗口中 - >保存工作簿作为启用宏的工作簿 .

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub

Dim x, dict

Dim i As Long, lr As Long

Dim Rng As Range, Cell As Range

Dim Str As String

lr = Cells(Rows.Count, 1).End(xlUp).Row

Set Rng = Range("A2:A" & lr)

x = Range("Days").Value

Set dict = CreateObject("Scripting.Dictionary")

If Target.Column = 2 And Target.Row > 1 Then

If Target.Offset(0, -1) <> "" Then

For Each Cell In Rng

If Cell <> "" And Cell = Target.Offset(0, -1) Then

If Str = "" Then

Str = Cell.Offset(0, 1).Value

Else

Str = Str & ", " & Cell.Offset(0, 1).Value

End If

End If

Next Cell

For i = 1 To UBound(x, 1)

If InStr(Str, x(i, 1)) = 0 Then

dict.Item(x(i, 1)) = ""

End If

Next i

On Error Resume Next

With Target.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:=Join(dict.keys, ",")

End With

End If

End If

End Sub

因此，一旦您在B列中选择了一个单元格，代码就会添加一个下拉列表，不包括为特定测试编号选择的日期 . A栏中的相应单元格.