Excel 三级 联动 下拉框 宏代码 实例

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 'Defin e 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 End With Exit For End If Next i ElseIf Target.Column = 2 Then Cells(Target.Row, Target.Column + 1) = "" Cells(Target.Row, Target.Column + 1).Validation.Delete For i = 2 To secondDrawBoxRowCount + 1 If Trim(Cells(Target.Row, Target.Column)) = Trim(Cells(i, secondDrawBoxColumn)) Then tempStr = Trim(Cells(i, secondDrawBoxColumn + 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 End If End Sub e 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 End With Exit For End If Next i ElseIf Target.Column = 2 Then Cells(Target.Row, Target.Column + 1) = "" Cells(Target.Row, Target.Column + 1).Validation.Delete For i = 2 To secondDrawBoxRowCount + 1 If Trim(Cells(Target.Row, Target.Column)) = Trim(Cells(i, secondDrawBoxColumn)) Then tempStr = Trim(Cells(i, secondDrawBoxColumn + 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 End If End Sub