Userform listbox search button

[Retroshift]

Hi, I have a search button in a userform to highlight/select the matching list item based on the value entered in an inputbox that is activated by this search button. I would like to be able to select the list item, regardless of case sensitivity. With the code below my entered value does not match if the list item contains upper case characters. How can I ignore case sensitivity in the search?
It should also recognize the list item if only a partially matching string has been entered (let's say a partial string of 3 characters).
The search criteria are the following: no blank entry field, the entry contains 3 dots/periods (to match the address format), the entry should end with "@xel.mail.com". Could these criteria be integrated in the error message below?

Private Sub CBSearchRecipient_Click()
Dim strInput10 As String

strInput10 = Trim(InputBox("Type the e-mail address of recipient you want to find.", "SEARCH RECIPIENT ADDRESS"))      'trim to avoid starting spaces in entry

With Me.lbxRecipients
    For i = 0 To .ListCount - 1
        If .List(1) = LCase(strInput10)
           .Selected(i) = True
          Exit For
        End If
    Next
End With

If strInput10 = "" Then           'add the two additional criteria of 'entry format contains 3 dots, and entry should end with "@xel.mail.com"'
MsgBox "Please enter a valid e-mail address.", vbExclamation, "INVALID ENTRY"
End If

End Sub

 

[Retroshift]

Anyone?

 

[Akuini]

The search criteria are the following: no blank entry field, the entry contains 3 dots/periods (to match the address format), the entry should end with "@xel.mail.com".

These are criteria for what you enter in the inputbox?
Could you give an example of what you enter in inputbox that meet those criteria?

 

[Retroshift]

Exactly. E-mail addresses. I finetuned the code below but I still would like to have the criteria of the "3 dots mandatory in the entry of the inputbox" (mail address format *.*.*.* => "firstname.lastname@xel.mail.com"), and the find partial match of 3 or more initial characters in order to select the corresponding listbox item address.

Private Sub CBSearchRecipient_Click()
Dim strInput10 As String
Dim pos As Long

strInput10 = Trim(InputBox("Type the e-mail address of recipient you want to find.", "SEARCH RECIPIENT ADDRESS")
            'Trim makes sure there are no single space searches
With Me.lbxRecipients
    For i = 0 To .ListCount - 1
        If LCase(.List(i)) = LCase(strInput10) Then        'include partial matches of 3 or more initial characters LEFT
           .Selected(i) = True
          Exit For
        End If
    Next
End With

pos = Len(strInput10) - InStrRev(strInput10, "@") + 1
If strInput10 = "" Or Right(strInput10, pos) <> "@xel.mail.com" Then                                     'Also check if entry contains 3 dots   "Or If Not strInput10 Like "*.*.*.*"" ???
        'checks if entry is blank/empty OR if entry ends on "@xel.mail.com"
MsgBox "Please enter a valid e-mail address.", vbExclamation, "INVALID ENTRY"
End If

End Sub

 

[Akuini]

I have an example of a searchable list box that might suit your needs.
How it works:
Type some keywords in the textbox, separated by a space, e.g "f ca"
The list in the listbox will be narrowed down as you type.
The search ignores the keywords order, so the keyword "ma la" would match "Maryland" and "Alabama".
Example:

userform - searchable, 1 column listbox, 1 textbox

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Image:

If you need, I can amend the code in the example above to meet your criteria. For example: you don't need to type "@xel.mail.com", it can be done automatically by macro.

But if that's not what you want then I'll try to amend your code to meet the criteria.

 

[Akuini]

Sorry, I forgot that the example above is for data with all uppercase characters, so it's case sensitive. To make it case insensitive just replace Sub get_filterX(ary) with this one:

Sub get_filterX(ary)
'search without keyword order, case insensitive
Dim i As Long, x, z, q
Dim v As String
Dim flag As Boolean
    
    d.RemoveAll
    z = Split(UCase(TextBox1.Value), " ")

    For Each x In ary
        flag = True: v = UCase(x)
            For Each q In z
                If InStr(1, v, q, vbBinaryCompare) = 0 Then flag = False: Exit For
            Next
        If flag = True Then d(x) = Empty
    Next

End Sub

 

[Retroshift]

Hi Akuini, thanks for your answers so far. I came across the TextBox1_Change() Sub but I would prefer the inputbox over the textbox. My code has an inputbox (via a button) as the userform is pretty crowded already which makes adding an extra textbox difficult. Would it be possible to apply your code above to an inputbox, or should an alternative inputbox approach maybe include a LEFT function to match corresponding initial characters?

Could it also be possible to include a verification if the entry in the inputbox contains 3 dots (mail address format "*.*.*.*" => "firstname.lastname@xel.mail.com") as a criterium?
Thanks