Setting an InputBox to be non-Case Sensitive

BrianExcel

Well-known Member
Joined
Apr 21, 2010
Messages
958
Hello -

I am using the following code which (1) Asks the user via InputBox to enter text to search for, (2) Asks the user via Input Box to enter the range to search, and (3) If the data is found, places "Text located" in the cell next to where the record was found.

Code:
Sub CommandButton1_Click()Dim r As Range, rAll As Range
Dim sTerm As String
    
    sTerm = Application.InputBox(Prompt:="Enter the text you wish to search for.", Title:="InputBox Method", Type:=2)
    Set rAll = Application.InputBox(Prompt:="Select a Range", Title:="InputBox Method", Type:=8)
 
    If rAll Is Nothing Then
        MsgBox "No Range Selected"
    Else
        For Each r In rAll
            If InStr(r, sTerm) Then
                r.Offset(0, 1) = "Text located"
            End If
        Next r
    End If
End Sub

Here is my issue though. I entered dummy data just to search and see where issues might be, and I noticed that the search and range needed are case sensitive. So for example, I entered "American" as one of the values.

If I search for "American" within the Range, it works great, but if I search "american" (lowercase a instead of upper), it doesn't locate the record.

That said, how do I set my input box to be non-case sensitive so the user can type either way and find what's there?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,830
Office Version
  1. 365
Platform
  1. Windows
It's not the InputBox you need to make case insensitive, it's the code that checks for the search term.

Try converting both the search term and the text you are searching you the same case, for example.
Code:
For Each r In rAll
            If InStr(UCase(r), UCase(sTerm)) Then
                r.Offset(0, 1) = "Text located"
            End If
        Next r
 

Watch MrExcel Video

Forum statistics

Threads
1,108,819
Messages
5,525,080
Members
409,618
Latest member
gkllc

This Week's Hot Topics

Top