CODEWITHSUNDEEP
excelvba
Paultechy
Paultechy

Reputation: 1

Auto Complete Excel VBA Issues with manual entry

Hi all having some troubles with Excel VBA code. I'm using multiple combo boxes, but running into issue, the company table is just a bunch of possible companies can choose from, also got code to allow for auto complete.

if box is empty and press tab fills in the first box with first item on the list (good to have)

If users types in something not on the list this is where it runs into problems namely. .Value = .List(0), and crashes the VBA with debug, (could not get the list property invalid propert array index). Since one of the combo boxes lists needs to ability to support, selecting from list or entering manually, would it be recommended doing a a Vlookup/Match inside VBA, or doing something like Value = Company, so what ever it entered stays their.

Any Assistance in this issue would be gratefully accepted.

Private Sub Company_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Debug.Print Time; "KeyDown"; KeyCode; Company.ListIndex; Company.ListCount, Company.Value
IsArrow = (KeyCode = vbKeyUp) Or (KeyCode = vbKeyDown)
If KeyCode = vbKeyReturn Then
    Me.Company.List = Worksheets("Company_Table").Range("A2", Worksheets("Company_Table").Cells(Rows.Count, "A").End(xlUp)).Value
ElseIf KeyCode = vbKeyTab Then
    'Tab key selects first displayed item or highlighted item
    With Me.Company
        If .ListIndex = -1 Then
            .Value = .List(0)
        Else
            .Value = .List(.ListIndex)
        End If
    End With
    KeyCode = vbKeyReturn
End If
End sub

Upvotes: 0

Views: 83

Answers (1)

Cameron Critchlow
Cameron Critchlow

Reputation: 1827

Try This

Private Sub Company_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    On Error GoTo ERRHAND
    Debug.Print Time; "KeyDown"; KeyCode; Company.ListIndex; Company.ListCount, Company.Value
    IsArrow = (KeyCode = vbKeyUp) Or (KeyCode = vbKeyDown)
    If KeyCode = vbKeyReturn Then
        Me.Company.List = Worksheets("Company_Table").Range("A2", Worksheets("Company_Table").Cells(Rows.Count, "A").End(xlUp)).Value
    ElseIf KeyCode = vbKeyTab Then
        'Tab key selects first displayed item or highlighted item
        With Me.Company
            If .ListIndex = -1 Then
                .Value = .List(0)
            Else
                .Value = .List(.ListIndex)
            End If
        End With
        KeyCode = vbKeyReturn
    End If
    Exit Sub
ERRHAND:
    Err.Clear
    Me.Company.Value = "Value Not Found"
End Sub

Upvotes: 0

Related Questions