ExcelVBA联想输入学习

哆哆Excel

发布于 2022-10-25 14:15:40

8060

发布于 2022-10-25 14:15:40

文章被收录于专栏：哆哆Excel

ExcelVBA联想输入学习

【问题】我想在明细表的“名称”列中输入内容时“联想输入”，数据来源于后面的“单价”表中。输入框输入时列表框引用相应的名称与单价，点击后在明细表的“名称”列与“单价”列中自动输入单价表中的相应的内容

代码如下：

Private Sub yhdinput_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim s, arr
    s = Me.yhdinput.Value
    Me.yhdListBox.Visible = 1
    Me.yhdListBox.Clear
    arr = Sheets("单价").[a1].CurrentRegion
    For i = 2 To UBound(arr)
        If InStr(arr(i, 1), s) Then Me.yhdListBox.AddItem arr(i, 1) & "|" & arr(i, 2)
    Next i
    Exit Sub
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Or Target.Areas.Count > 1 Then GoTo 100: Exit Sub
If Target.Column <> 5 Or Target.Row < 3 Then GoTo 100: Exit Sub
        With Me.yhdinput
            .Visible = True
            .Top = ActiveCell.Top
            .Left = ActiveCell.Left
            .Width = ActiveCell.Width
            .Height = ActiveCell.Height
            .Value = ""
            .Activate
        End With
        With Me.yhdListBox
            .Visible = True
            .Top = ActiveCell(1, 2).Top
            .Left = ActiveCell(1, 2).Left
            .Width = ActiveCell.Width * 1.5
            .Height = ActiveCell.Height * 8
            .Clear
        End With
        Exit Sub

100:
        Me.yhdinput = ""
        Me.yhdListBox.Clear
        Me.yhdinput.Visible = False
        Me.yhdListBox.Visible = False
End Sub

Private Sub yhdListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim t_arr
    t_arr = Split(Me.yhdListBox.Value, "|")
    ActiveCell.Value = t_arr(0)
    ActiveCell.Offset(0, 3).Value = t_arr(1)
    Me.yhdinput.Value = ""
    Me.yhdListBox.Clear
    Me.yhdinput.Visible = False
    Me.yhdListBox.Visible = False
'    ActiveCell.Offset(1, 0).Select
End Sub

Private Sub yhdListBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
 Dim t_arr
If KeyCode = 13 Then
    t_arr = Split(Me.yhdListBox.Value, "|")
    ActiveCell.Value = t_arr(0)
    ActiveCell.Offset(0, 3).Value = t_arr(1)
    Me.yhdinput.Value = ""
    Me.yhdListBox.Clear
    Me.yhdinput.Visible = False
    Me.yhdListBox.Visible = False

End If
End Sub

【坑】开始我是用这代码，结果出现如果选中最左边一格（全选）会出现溢出

If Target.Count > 1 Then Exit Sub