用EXCEL实现三级联动的vba代码

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim tempStr As String

Dim firstDrawBoxRowCount As Integer
Dim firstDrawBoxColumn As Integer

firstDrawBoxRowCount = 15   --三级联动第一级的单元格行数 ---隐藏的单元格
firstDrawBoxColumn = 1        ---三级联动第一级的单元格列数---隐藏的单元格

Dim secondDrawBoxRowCount As Integer
Dim secondDrawBoxColumn As Integer

secondDrawBoxRowCount = 84 ----三级联动第二级的单元格行数---隐藏的单元格
secondDrawBoxColumn = 4  ----三级联动第二级的单元格列数 ---隐藏的单元格

If Target.Column = 13 Then ------------三级联动第一级的单元格列数---选择第一级显示第二级
     Cells(Target.Row, Target.Column + 1) = ""
     Cells(Target.Row, Target.Column + 2) = ""
     For i = 2 To firstDrawBoxRowCount + 1
        If Trim(Cells(Target.Row, Target.Column)) = Trim(Cells(i, firstDrawBoxColumn)) Then
            tempStr = Trim(Cells(i, firstDrawBoxColumn + 1))
            Cells(Target.Row, Target.Column + 1).Select
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:=tempStr
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .IMEMode = xlIMEModeNoControl
                    .ShowInput = True
                    .ShowError = True
                End With
            Exit For
        End If
     Next i
ElseIf Target.Column = 14 Then------三级联动第二级的单元格列数---选择第二级显示第三级
    For i = 2 To secondDrawBoxRowCount + 1
        If Trim(Cells(Target.Row, Target.Column)) = Trim(Cells(i, secondDrawBoxColumn)) Then
            tempStr = Trim(Cells(i, secondDrawBoxColumn + 1))
             If tempStr = "" Then
               tempStr = " "
             End If
            Cells(Target.Row, Target.Column + 1).Select
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:=tempStr
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .IMEMode = xlIMEModeNoControl
                    .ShowInput = True
                    .ShowError = True
                End With
            Exit For
        End If
     Next i
End If

End Sub

 

 

红色部分是用宏录制的vba

我做了个省市区三级联动的简单例子http://download.youkuaiyun.com/source/1495163 ，免分下载，呵呵，

有不明白的，可以留言问。

好了，肚子饿了去吃饭啦！！！