Automation Error on Keypress in Userform Listbox

[stuli1989]

I'm trying to create a Auto-fill Textbox where my users are restricted to options that are already present in a pre-populated list of Assets.

I've managed to make it work to the point that when they start typing the letters the Listbox shows up with possible matches and if they double click on a particular match that get's selected and entered into the text box and they can continue.

I got early feedback though that the users in my firm would prefer if they could use the down arrow and then press enter to select the Asset from the list box.

Here's the code I'm using to make that happen:

Private Sub lbAssetID_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim i As Long
    
    If KeyAscii = 13 Then
        With Me.lbAssetID
            For i = 0 To .ListCount - 1
                If .Selected(i) Then
                    Me.tbAssetID.Value = .List(i, 0)
                    lbAssetID.Visible = False
                    Exit For
                End If
            Next
        End With
    End If
End Sub

However while it works smoothly for Double Click it fails for Keypress giving me the error:
Automation Error
The object invoked has disconnected from its clients

I understand it might be complex to answer without the file so I've made that available at this link:
Blotter v3.xlsm

Would appreciate any ideas to making it work! Thanks!

 

[NdNoviceHlp]

Hi stuli1989 and Welcome to the Board! Unfortunately, I don't have a solution for U. I thought it might be helpful for U to know that I can recreate your error. I messed with it for over an hour and have given up. It's very mysterious why the error occurs and why it can't be fixed? The userform itself seems to want to close which causes the error. I'm guessing the only fix is trialling a different approach. Good luck. Dave

 

[CountTepes]

Have you tried putting a breakpoint in, at the first instruction and stepped through to see where it falls over?

 

[Jimmypop]

I also recreated the error and have had a look and problem seems to be at

lbAssetID.Visible = False'I changed it to true and it did not give automation error. It just stayed visible. i even removed _
it and inserted a msgbox which worked fine. i then added the false property again to hide listbox and error was back

Do not know why.... but will have a look when at work again for alternative solution

 

[CountTepes]

It might just be an Excel thing.

I have been having a similar error on:

Application.EnableEvents = False