|
|
楼主 |
发表于 2011-6-24 07:32
|
显示全部楼层
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If Target.Count > 1 Or Target.Row = 1 Then Exit Sub
- If Target.Column <> 1 And Target.Column <> 2 Then Exit Sub
- Dim dic As Object
- Dim arr As Variant
- Dim i As Integer
- Dim j As Integer
- Dim di As Object
- Dim k As Variant
- Set dic = CreateObject("scripting.dictionary")
- Set di = CreateObject("scripting.dictionary")
- With Sheets("sheet3")
- arr = .Range("a2:b" & .Cells(Rows.Count, 1).End(xlUp).Row)
- End With
- If Target.Column = 1 Then
- For i = 1 To UBound(arr)
- dic(arr(i, 1)) = ""
- Next i
- With Target.Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
- Operator:=xlBetween, Formula1:=Join(dic.keys, ",")
- End With
- ElseIf Target.Column = 2 Then
- If Target.Offset(0, -1) = "" Then Exit Sub
- For j = 1 To UBound(arr)
- If arr(j, 1) = Target.Offset(0, -1) Then
- di(arr(j, 2)) = ""
- End If
- Next j
- With Target.Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
- Operator:=xlBetween, Formula1:=Join(di.keys, ",")
- End With
- k = di.keys
- Target = k(0)
- End If
- End Sub
最新更新，，，近期正在学习字典。以前没有发现。呵呵，字典真是太好了。。。比collection强多了。。。个人认为。
[ 本帖最后由 ctp_119 于 2011-6-24 07:34 编辑 ] |
|
楼主: ctp_119
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2011-6-16 13:05
发表于 2011-6-24 22:28
