Excel Userform Listbox Search issues

[cerberus1845]

Hi,

I'm hoping someone can help me as I've hit a bit of a brick wall. I've done a lot of reading on the web and on here and have started to put this together to try and build my solution - but I've hit an issue I hope you might be able to help with.

What I'm trying to do/achieve:
I have an excel spreadsheet with several sheets. One of the sheets is called 'PRLUM' and this has a table of 12 columns called ''tblPRLUM". I want to have a userform that allows the user to search on some of the columns in this table and then populate a Listbox with the results. If they select a row in the populated listbox then this populated a series of 12 text boxes below with whatever they have selected. The user can then make changes to any of these text boxes and then presses an 'UPDATE' button which then writes the changes back to the correct row. I also want to be able to create a new row which checks for duplicates on one of the columns and will not allow the addition of the row if duplicates exist.

The code I've tried to use:

'search functionaility WIP
Private Sub TextBox13_Change()
    Dim ws As Worksheet             ' declare data sheet as WorkSheet
    Set ws = Sheet1             ' << define data sheet's Code(Name)
    With Me.ListBox1
        .Clear                                  ' remove any prior items from listbox
        .List = ws.Range("A1:L1").Value2        ' display head & provide for sufficient columns
    End With
    If Me.TextBox13.Text = "" Then Exit Sub      ' no further display, so escape
    Dim SearchText As String
    SearchText = StrConv(Me.TextBox13.Text, vbProperCase)
    If Me.TextBox13.Text <> SearchText Then      ' avoid double call of Change event
        Me.TextBox13.Text = SearchText           ' display ProperCase
        Exit Sub                                ' force 2nd call after text change
    End If
    With ws
        Dim i As Long
        For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            Dim lngth As Long: lngth = Len(SearchText)
            Dim x As Long
            For x = 1 To 12                         ' range columns
                Dim found As Boolean
                If Left(.Cells(i, x).Value, lngth) = SearchText Then
                    Me.ListBox1.AddItem .Cells(i, x).Value
                    Dim c As Long
                    For c = 1 To 11
                        Me.ListBox1.List(ListBox1.ListCount - 1, c) = .Cells(i, c + 1).Value
                    Next c
                    found = True                    ' check for 1st occurrence avoiding redundant loops
                End If
                If found Then
                    found = False
                    Exit For                        ' 1st finding suffices
                End If
            Next x
        Next i
    End With
End Sub

one of the issues seems to be that it seems to search - but also includes the search term in the first column of the textblock.. I'm also not sure how to resolve this and then build in an update/add functionality.

Any help would be greatly appreciated!! - also - please don't get hung up on the code I've posted above.. if there is any other code that would meet my requirements then please feel free to share!

 

[Akuini]

@cerberus1845
Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

 

[cerberus1845]

@cerberus1845
Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

hi thanks for your reply - appreciate it!!.. i forgot to come back to this post and update it as i now resolved the issue... (was user error!! lol!) -all sorted now!

 

[Akuini]

Ok, glad you figured it out.