Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

function UCASE doesn't work with small letters

0

Hi 

I  try  to  show  the  written  item before  show  message box  by  small letters for  combobox  , then should  show message , but   it  shows  by  big letters even  if  I write  by  small letter( there are  items are  already by big letters  in combobox and  just  show by  big letters even  if  I  write the item by  small letters) I  used  function UCASE for  combobox  , is  that  right?

Private Sub ComboBox1_Change()
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 = ""
    End If
End If
End Sub
    

how  can  I do  that?

Answer
Discuss

Discussion

Maklil

Not sure about your question so would like to see a file. Please edit your question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing macro and data. That will save me trying to re-create your problem and give you specific help.
John_Ru (rep: 6102) Aug 15, '22 at 11:47 am
Hi John 
I  no  know  if  the  file  help you   if  you  still don't  understand  what  I  try  it 
ok  you  can  see  the   file.
MAKLIL (rep: 34) Aug 16, '22 at 4:45 am
Thanks Maklil.  I will look at your file when I get home soon (and can run yiur UserForm). 

Is the problem that you try to type "atr" say but the ComboBox changes that to ATR-100 or similar? 
John_Ru (rep: 6102) Aug 16, '22 at 5:22 am
Add to Discussion

Answers

0
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.

Discuss

Discussion

@Don - I've added a file above but clicking it does NOT downlaod the file- it just directs the user to another (empty) page. Kindly fix when you can.
John_Ru (rep: 6102) Aug 16, '22 at 6:30 am
Hi John,
but you may not be able to open it
yes this  is  not  only  problem  ,also  when  post   new thread   it doesn't  post  directly and it  inform  me  it  will take  time  to  post  it  
thanks  for  your  solution .
MAKLIL (rep: 34) Aug 16, '22 at 7:06 am
Thanks for selecting my Answer, Maklil- glad it worked for you.

Sadly the delay in posting new questions relates to spam- you may have noticed last week there were many crazy messages (about black magic, love solutions etc.). Don needed to get rid of that spam but sadly it means that genuine users pay a price (an added delay), not the spammer.
John_Ru (rep: 6102) Aug 16, '22 at 7:17 am
Maklil- did you get an email saying that I'd provided an Answer? I didn't get an email to say you'd selected my Answer (or added to the Discussion) but had the Forum page up and noticed.
John_Ru (rep: 6102) Aug 16, '22 at 7:34 am
you may have noticed last week there were many crazy messages (about black magic, love solutions etc.).
yes
 did you get an email saying that I'd provided an Answer? 
yes 
MAKLIL (rep: 34) Aug 16, '22 at 8:02 am
Thanks Maklil
John_Ru (rep: 6102) Aug 16, '22 at 8:11 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login