|
我在sheet6表中L列第四单元格开始《客户(公司)名称》“
输入时逐步提示信息.rar
(12.86 KB, 下载次数: 675)
”也在sheet6表中输入下列代码,一运行就说"方法和数据成员未找到",然后Private Sub Worksheet_SelectionChange(ByVal Target As Range)语句为黄色
注(数据库在sheet35表,“”数据在sheet35表中AB列第五单元开始)
要求1:输入时逐步提示信息
要求2: 模糊提示信息(即输入其中一个“X或N”字,则自动列出含有“X或N”的名称供选择)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row < 3 Then Exit Sub
Dim d, i&, Myr&, Arr, cj, cp, jg, r1, n&, ii&
Dim x, cpin$, Myr1&, r%, Arr1(), j&
Myr = Sheet35.[AB65536].End(xlUp).Row
Arr = Sheet35.Range("AB5:AB" & Myr)
col = Target.Column
Me.ListBox1.Clear
Me.TextBox1 = ""
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
If Target.Column = 2 Then
For i = 1 To UBound(Arr)
cj = cj & Arr(i, 1) & ","
Next
cj = Left(cj, Len(cj) - 1)
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=cj
End With
ElseIf Target.Column = 3 Then
Myr = Sheet35.[AB65536].End(xlUp).Row
Arr = Sheet35.Range("AB5:AB" & Myr)
For i = 1 To UBound(Arr)
cp = cp & Arr(i, 1) & ","
Next
cp = Left(cp, Len(cp) - 1)
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=cp
End With
ElseIf Target.Column = 4 Or Target.Column = 5 Then
With Me.TextBox1
.Visible = True
.Top = Target.Top
.Left = Target.Left
.Width = Target.Width
.Height = Target.Height + 5
.Activate
End With
With Me.ListBox1
.Visible = True
.Top = Target.Top
.Left = Target.Left + Target.Width
.Height = Target.Height * 10
End With
If Target.Column = 4 Then
With Me.ListBox1
.List = kc
End With
ElseIf Target.Column = 5 Then
With Me.ListBox1
.List = kd
End With
End If
Else
Me.ListBox1.Clear
Me.TextBox1 = ""
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
End If
End Sub
|
|