产品名称的数据有效性可以用下面的代码
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim row1, arr1, d, I, s
If Target.Row > 7 And Target.Row < 14 And Target.Column = 2 Then
row1 = Sheets("信息表").Range("B65536").End(xlUp).Row
arr1 = Sheets("信息表").Range("B2:B" & row1)
Set d = CreateObject("Scripting.Dictionary")
For I = 1 To UBound(arr1)
If Not d.Exists(arr1(I, 1)) Then
d.Add arr1(I, 1), ""
End If
Next
s = Join(d.KEYS, ",")
With Target.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=s
End With
End If
End Sub |