Selected Answer
Maklil
With default settings, if you type "a" is your ComboBox, it will show a matching entry from the RowSource e.g."ATR-100".
That's because the ComboBox default setting for the MatchEntry property is
1 -fmMatchEntryComplete
Change it to
2- fmMatchEntryNone
and a lower case letter "a" can be typed (without showing ATR-100) BUT your ComboBox1_Change macro is triggered as each letter is typed (and says "not matched" and sets it the value back to "").
I suggest you change the macro from _Change to _AfterUpdate as per the bold changes below- that means it will work if a drop down is selected or a string like "atr-102" is typed then Enter is pressed:
Private Sub ComboBox1_AfterUpdate()
Dim ws As Worksheet, rng As Range, cb
Set ws = Sheets("sheet1")
cb = Me.ComboBox1.Value
If cb <> "" Then
Set rng = ws.Columns(2).Find(UCase(cb), LookAt:=xlWhole)
If rng Is Nothing Then
MsgBox "Not matched"
Me.ComboBox1.Value = ""
Else
' set the value to CAPS if found
Me.ComboBox1.Value = UCase(cb)
End If
End If
End Sub
Not sure why you used UCase(Cb) in the .Find statement since Range.Find is case-insensitive by default. You can force it to be case-sensitive by setting the MatchCase argument (default= False) as follows:
Set rng = ws.Columns(2).Find(LCase(cb), LookAt:=xlWhole, MatchCase:=True)
Hope this helps. I will attach a modified file but you may not be able to open it (following some site changes at the weekend). If so, please copy the code into your file (deleting or overwriting the ComboBox1_Change code) and chnage the CB property as descibed.