|
楼主 |
发表于 2016-2-22 22:38
|
显示全部楼层
scl5801 发表于 2016-2-22 14:33
把你的解决方案分享一下行吗?
您好,我的解决方案如下:- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If Target.Columns.Count > 1 Or Target.Rows.Count > 1 Then Exit Sub
- If Target.Column < 4 Or Target.Column > 5 Then Exit Sub
- arr = Sheets("数据源").[a1:m7]
- If Target.Column = 4 Then
- r = Application.Transpose(Sheets("数据源").[b2].Resize(1, UBound(arr, 2) - 1))
- ElseIf Target.Column = 5 Then
- If Target.Offset(0, -1) <> "" Then
- Set Rng = Sheets("数据源").Rows("2:2").Find(Target.Offset(0, -1))
- r = Rng.Offset(1, 0).Resize(UBound(arr) - 2, 1)
- Else: Exit Sub
- End If
- End If
- For i = 1 To UBound(r)
- If r(i, 1) <> "" Then s = s & "," & r(i, 1)
- Next
- s = Mid(s, 2)
- With Target.Validation
- .Delete
- .Add xlValidateList, , , s
- End With
- End Sub
复制代码 |
|