|
本帖最后由 ysjjess 于 2020-1-27 09:47 编辑
大家新年好!鼠年吉祥!希望老师指点一下:在读取数据源工作表里没有的数据后,回车不输入到单元格,报错:excel 运行错误381,无法设置List属性。属性阵列索引无效,点击调试指向下面红色代码。部分代码如下:Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
For i = 0 To 2
ActiveCell.Offset(0, i) = Me.ListBox1.List(Me.ListBox1.ListIndex, i)"这句代码”不知道要怎么改才能不出错和在数据源里没有的内容回车输入到单元格。多谢!
Next
ActiveCell.Offset(, 3).Select
End If
End Sub
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim i&, j%, t%, myStr$, k&, N$, P$
Dim LG As Boolean, Arr1()
Me.ListBox1.Clear
myStr = UCase(Me.TextBox1.Value)
For i = 1 To Len(myStr)
If Asc(Mid$(myStr, i, 1)) < 0 Then LG = True: Exit For
Next
arr2 = Array("编码", "图号", "名称")
k = k + 1
ReDim Arr1(1 To 3, 1 To k)
For i = 1 To 3
Arr1(i, k) = arr2(i - 1)
Next
For i = 1 To UBound(Arrsj)
s = Arrsj(i, 1) & Arrsj(i, 2) & Arrsj(i, 3)
N = ""
If LG Then
N = s
Else
For j = 1 To Len(s)
P = Mid(s, j, 1)
If Asc(P) < 0 Then N = N & PinYin(P) Else N = N & P
Next
End If
If InStr(N, myStr) Then
k = k + 1
ReDim Preserve Arr1(1 To 3, 1 To k)
For t = 1 To 3
Arr1(t, k) = Arrsj(i, t)
Next
End If
Next i
If t = 0 Then Exit Sub
Me.ListBox1.List = Application.Transpose(Arr1)
Me.ListBox1.Selected(1) = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
If Target.Count = 1 Then
If Target.Column = 2 And (Target.Row > 5 And Target.Row < 21 Or Target.Row > 53 And Target.Row < 62) Then '2列大于5行小于21行和大于53行小于62行弹框有效
With Sheets("货品资料")
Myr = .[F65536].End(xlUp).Row + 1
Arrsj = .Range("F2:H" & Myr)
Me.ListBox1.List = Arrsj
Me.ListBox1.Selected(0) = True
End With
TextBox1.Activate
TextBox1 = ""
With Me.TextBox1
.Visible = True
.Top = Target.Top
.Left = Target.Left
.Width = Target.Width
.Height = Target.Height
End With
With Me.ListBox1
.Visible = True
.Top = Target.Top
.Left = Target.Left + Target.Width
.Width = Target.Width * 3
.Height = Target.Height * 5
End With
Else
Me.ListBox1.Clear
Me.TextBox1 = ""
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
End If
|
|