|
例8的变体,原来的例子里面字典的item属性是空的,我利用了一下,可以不用再在后面写循环判断了
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 4 And Target.Column <> 3 Then Exit Sub
Dim d, i&, Myr&, Arr, r%, Arr1(), cp$, ks&, js&, j&, ymc
Set d = CreateObject("Scripting.Dictionary")
Myr = Sheet1.[b65536].End(xlUp).Row
Arr = Sheet1.Range("a2:b" & Myr)
'字典d的key是源名称,item是代号合集
For i = 1 To UBound(Arr)
If Arr(i, 1) <> "" Then
ymc = Arr(i, 1) '源名称
d(ymc) = Arr(i, 2)
Else
d(ymc) = d(ymc) & "," & Arr(i, 2)
End If
Next
If Target.Column = 3 Then
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(d.keys, ",")
End With
Target.Offset(0, 1) = ""
ElseIf Target.Column = 4 And Target.Offset(0, -1) <> "" Then
cp = d(Target.Offset(0, -1).Value)
'必须引用单元格的Value或者Text属性,否则vb以为要新增一个range对象的key
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=cp
End With
Target = Split(cp, ",")(0)
End If
Set d = Nothing
End Sub
|
|