|
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Target = "" Then
Target.Offset(0, 2).Clear
End If
Set Rng = Sheets(2).Columns(1).Find(Target, lookat:=xlWhole)
If Rng Is Nothing Then Exit Sub
str1 = ""
For j = 2 To Sheets(2).Cells(Rng.Row, Columns.Count).End(xlToLeft).Column
If Len(Sheets(2).Cells(Rng.Row, j)) > 0 Then str1 = str1 & "," & Sheets(2).Cells(Rng.Row, j)
Next j
If Len(str1) = 0 Then Exit Sub
With Target.Offset(0, 2).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Mid(str1, 2)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With
End Sub
|
评分
-
2
查看全部评分
-
|