Setting an InputBox to be non-Case Sensitive

BrianExcel

Well-known Member
Joined
Apr 21, 2010
Messages
969
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?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,827
Messages
5,855,878
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top