用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.csdn.net/source/1495163 ,免分下载,呵呵,

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

好了,肚子饿了去吃饭啦!!!

  • 2
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 13
    评论
Excel 三级 联动 下拉框 宏代码 实例 代码注释 先在第一个下拉框加入一个valiation, 内容是 =$A$2:$A$5 Private Sub Worksheet_Change(ByVal Target As Range) ' Call back function which defined within according worksheet Dim i As Integer Dim tempStr As String Dim firstDrawBoxRowCount As Integer Dim firstDrawBoxColumn As Integer firstDrawBoxRowCount = 4 'Define the row number of first draw box firstDrawBoxColumn = 1 'Define the column number of ifrst draw box Dim secondDrawBoxRowCount As Integer Dim secondDrawBoxColumn As Integer secondDrawBoxRowCount = 33 'Define the row number of second draw box secondDrawBoxColumn = 4 'Define the column number of second draw box If Target.Column = 1 Then 'This defines the first column of draw box list, you can also define the row number of draw box list Cells(Target.Row, Target.Column + 1) = "" ' Do the clean first Cells(Target.Row, Target.Column + 1).Validation.Delete Cells(Target.Row, Target.Column + 2) = "" Cells(Target.Row, Target.Column + 2).Validation.Delete For i = 2 To firstDrawBoxRowCount + 1 'Enter the cycle to find out the content for column 2 If Trim(Cells(Target.Row, Target.Column)) = Trim(Cells(i, firstDrawBoxColumn)) Then tempStr = Trim(Cells(i, firstDrawBoxColumn + 1)) 'Find out the options for second draw box, it is seperated by , Cells(Target.Row, Target.Column + 1).Select ' Fill the validation to second draw box 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

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 13
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值