|
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If Target.Row = 1 Then Exit Sub
- If Target.Count > 1 Then Exit Sub
- arr = Sheet1.[a1].CurrentRegion '科目表
- If Target.Column = 1 Then '第一列有效性
- For i = 2 To UBound(arr)
- If Len(arr(i, 1)) > 0 Then xstr = xstr & "," & arr(i, 1)
- Next
- ElseIf Target.Column = 2 Then '第二列有效性
- For i = 2 To UBound(arr) '找到一级科目所在的行
- If arr(i, 1) = Target.Offset(0, -1) Then Exit For
- Next
- If i <= UBound(arr) Then '表示找到行
- For j = 2 To UBound(arr, 2)
- If Len(arr(i, j)) > 0 Then xstr = xstr & "," & arr(i, j)
- Next
- End If
- End If
- If Len(xstr) > 0 Then '生成有效性
- With Target.Validation
- .Delete
- .Add xlValidateList, Formula1:=Mid(xstr, 2)
- End With
- End If
- End Sub
复制代码 |
|