|
发表于 2016-4-12 14:55
|
显示全部楼层
本楼为最佳答案
代码小改了一下,二级菜单去重。
加了说明。- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If Target.Count > 1 Then Exit Sub '选择单元格数超过1个,退出
- c = Target.Column 'c=单元格列
- If c < 5 Or c > 6 Then Exit Sub '非5非6列,退出
- Set d = CreateObject("scripting.dictionary") '设立字典d
- arr = [b1].CurrentRegion '源数据
- For i = 1 To UBound(arr)
- x = arr(i, 1) '第一列为key
- If InStr(d(x), arr(i, 2)) = 0 Then d(x) = d(x) & "," & arr(i, 2) '第二列去重为item
- Next
-
- '如果单元格在第5列,以字典的key相连为有效性,否则以第五列的key对应的item为有效性
- xstr = IIf(c = 5, Join(d.keys, ","), Mid(d(Target.Offset(0, -1).Value), 2))
-
- If Len(xstr) Then '如果有效性非空,建立有效性
- With Target.Validation '建立有效性的固定语句
- .Delete
- .Add xlValidateList, , , xstr
- End With
- End If
- End Sub
复制代码 |
|