Display textbox from listbox - getting 381 error

[maiatanaka]

Sample File

I have a userform that I created with a listbox and textboxes. You can fill customer information with the textbox and save it and the listbox shows the customers. The hope is to have the listbox to populate the textboxes from a double click action but I'm getting a "Run-time error '381': Could not get the List property. Invalid proeprty array index"

I've used some code I found online and fit it to my criteria so the listbox is populated by .listcount (as it gets updated if someone puts in a name in the First Name textbox (txtCustFirstname) and I'm not sure if that's affecting how the doubleclick action.

This populates the listbox:

Private Sub txtCustFirstName_Change()
On Error Resume Next

Dim wsCustomer As Worksheet
Set wsCustomer = Worksheets("Customer")

Dim r, lastrow As Integer
lastrow = wsCustomer.Range("A10000").End(xlUp).Row

If Me.txtCustFirstName.Text = vbNullString Then
    listCustomer.List = wsCustomer.ListObjects("CustTable").DataBodyRange.Value2
    Exit Sub
   
Else
 
 listCustomer.Clear

'found here https://www.youtube.com/watch?v=zaPpkWYnAN4

For r = 2 To lastrow

    a = Len(txtCustFirstName.Text)
    If UCase(Left(wsCustomer.Cells(r, "B").Value, a)) = UCase(Me.txtCustFirstName.Text) Then
        With Me.listCustomer
       
                .AddItem wsCustomer.Cells(r, "A").Value
                .List(.ListCount - 1, 1) = wsCustomer.Cells(r, "B").Value
                .List(.ListCount - 1, 2) = wsCustomer.Cells(r, "C").Value
                .List(.ListCount - 1, 3) = wsCustomer.Cells(r, "D").Value
                .List(.ListCount - 1, 4) = wsCustomer.Cells(r, "E").Value
                .List(.ListCount - 1, 5) = wsCustomer.Cells(r, "F").Value
                .List(.ListCount - 1, 6) = wsCustomer.Cells(r, "G").Value
                .List(.ListCount - 1, 7) = wsCustomer.Cells(r, "H").Value
        End With
    End If
Next r
Exit Sub

End If

This populates the double click action

Private Sub listCustomer_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

'listing customer fields from listbox

Dim i As Long

With Me.listCustomer
   For i = .ListCount - 1 To 1 Step -1 'found here: https://forum.ozgrid.com/forum/index.php?thread/144222-search-listbox-item-and-upon-double-click-selected-item-appear-on-subform-in-txt/
   'For i = 0 To .ListCount - 1 *was trying different things
   'i = listCustomer.ListCount - 1
 
   If .Selected(i) Then
    DoEvents
             Me.txtCustFirstName.Value = .List(i, 1)
             Me.txtCustLastName.Value = .List(i, 2)
             Me.txtCustPhone.Value = .List(i, 4)
             Me.txtCustEmail.Value = .List(i, 3)
             Me.txtCustOrganization.Value = .List(i, 5)
           
            If .List(i, 6) = "Y" Then
           
                 Me.btnCustNonProfitY = True
                 Me.txtCustNonProfitNum.Value = .List(i, 7)
               
             Else
                Me.btnCustNonProfitN = False
            End If
    End If
   
    Next i
   
End With


End Sub

Would love it if someone could educate me on listboxes and listcounts because I'm been trying to read up on different sites and for sure there are different ways to populate a listbox and to grab information from listboxes (including .listbox.columns(A) as an example) so I'm not sure where I'm going wrong here...

 

[dmt32]

Hi,
link provided states file has been deleted.

Dave

 

[maiatanaka]

Here's a new link:

Sample File

 

[rlv01]

The error is because your text box event code is clearing your listbox list:

If Me.txtCustFirstName.Text = vbNullString Then
    listCustomer.List = wsCustomer.ListObjects("CustTable").DataBodyRange.Value2
    Exit Sub
  
Else
 
 listCustomer.Clear  '<---- this is your problem

'found here https://www.youtube.com/watch?v=zaPpkWYnAN4

You need a way to control when the event code is executed. Application.EnableEvents does not work in Userforms so what you can do instead is:
1. Add a module level public variable Public SkipEvents As Boolean
2. In the form's Initialize subroutine add this

Private Sub UserForm_Initialize()
    Me.SkipEvents = False 'don't skip event code

3. At the top of Sub txtCustFirstName_Change add this code:

Private Sub txtCustFirstName_Change()
    If Me.SkipEvents = True Then
        Exit Sub
    End If

4. Finally, in Sub listCustomer_DblClick modify your code like this:

                Me.SkipEvents = True                  'skip event code
                Me.txtCustFirstName.Value = .List(i, 1)
                Me.txtCustLastName.Value = .List(i, 2)
                Me.txtCustPhone.Value = .List(i, 4)
                Me.txtCustEmail.Value = .List(i, 3)
                Me.txtCustOrganization.Value = .List(i, 5)
                Me.SkipEvents = False                 're-enable event code

Also you might want to review your other text box change code and add

    If Me.SkipEvents = True Then
        Exit Sub
    End If

anywhere else you think it might be needed.

 

[maiatanaka]

Add a module level public variablePublic SkipEvents As Boolean

Sorry, still learning VBA. I'm getting an error on the Me.SkipEvents in my form so I'm assuming I'm not declaring it properly.

I added a module inside the file but how should I be declaring the SkipEvents?

UPDATE: added the public variable to the userform. Works!