Search For Upper or Lower Case Values

[LikeButtah1]

Here is the code I'm using to search for a Last Name and return the entire row of values into separate boxes on a user form. When I type the Last Name into the text box to search it must be with the Upper case first letter because if I don't capitalize the Last Name excel doesn't recognize it and nothing gets returned into the text boxes. Is there a way in vba to ignore whether or not the Last Name has a capitalized first letter. Thanks in advance.

Private Sub btnLName_Click()

                blnNew = False
                txtLastName.Text = ""
                txtFirstName.Text = ""
                txtC.Text = ""
                txtNewCard.Text = ""
                txtAccDes.Text = ""
                txtAccCreated.Text = ""
                txtAccGranted.Text = ""
                txtAccCancelled.Text = ""
                txtAccCategory.Text = ""
                txtNotes.Text = ""
   
    TRows = Worksheets("PNG Database").Range("A1").CurrentRegion.Rows.Count
    For i = 3 To TRows
        If Worksheets("PNG Database").Cells(i, 1).Value = TextBox1.Text Then
                
            txtLastName.Text = Worksheets("PNG Database").Cells(i, 1).Value
            txtFirstName.Text = Worksheets("PNG Database").Cells(i, 2).Value
            txtC.Text = Worksheets("PNG Database").Cells(i, 3).Value
            txtNewCard.Text = Worksheets("PNG Database").Cells(i, 4).Value
            txtAccDes.Text = Worksheets("PNG Database").Cells(i, 5).Value
            txtAccCreated.Text = Worksheets("PNG Database").Cells(i, 6).Value
            txtAccGranted.Text = Worksheets("PNG Database").Cells(i, 7).Value
            txtAccCancelled.Text = Worksheets("PNG Database").Cells(i, 8).Value
            txtAccCategory.Text = Worksheets("PNG Database").Cells(i, 9).Value
            txtNotes.Text = Worksheets("PNG Database").Cells(i, 12).Value
         
            Exit For
        End If
    Next i
    
   


End Sub

 

[Jerry Sullivan]

One method is to place this at the top of the code module.

Option Compare Text

This will make comparisons text-insensitive for the entire module.

Another method is to set the case of both strings being compared to either lower or upper case.

 If LCase(Worksheets("PNG Database").Cells(i, 1).Value) = LCase(TextBox1.Text) Then

The second method is more robust since it doesn't rely on the Option statement.

 

[LikeButtah1]

Jerry,
Thank you so much. I used the 2nd method and it works perfectly. I really appreciate it.