View original (French)

Drop-down list + VBA, issues with uppercase letters

Solved
Anthelm Posted messages 202 Status Member -  
Yoyo01000 Posted messages 1720 Registration date   Status Member Last intervention   -
Hello,

I have a dropdown list with "Yes" and "No" as choices.
I have the following code:
If Worksheets("Options").Range("B2") = ("Yes") Then
option1 = "yes"

When the user clicks "Yes" in the list, I get a capital letter, so there is no problem.
But if they type "yes" on the keyboard, the cell accepts it, but the macro (which considers that "yes" is not equal to "Yes") does not.

I thought about If Worksheets("Options").Range("B2") = ("Yes") OR (the same thing) = ("yes") Then (etc etc)
It would work if the user entered "yes" with or without a capital letter.

But that's a bit silly because someone could always type "yeS" or "yEs" due to a typo... I exaggerate a bit, but it's not optimal in itself. Especially since the user won't realize that the result in VBA won't return the right thing!

In your opinion, what should be reviewed?
1) Is the dropdown list a bad choice (maybe a checkbox would be better, for example?)
2) Is the macro too simple, and with another coding approach, can we avoid this "case sensitivity"?

Thank you for giving me a lead if you have experienced a similar situation...

Have a great day everyone!

3 answers

Answer 1 / 3
Yoyo01000 Posted messages 1720 Registration date   Status Member Last intervention   168
 
Hello, why go through VBA for this?

It would be enough to create a dropdown menu without VBA!
0
Answer 2 / 3
Yoyo01000 Posted messages 1720 Registration date   Status Member Last intervention   168
 
Tab "Data" and then "Data Validation", in "Options" choose "List" and enter "Yes;No"
0
Anthelm Posted messages 202 Status Member 1
 
I believe you didn't read the topic in its entirety :) But thanks anyway!
0
Yoyo01000 Posted messages 1720 Registration date   Status Member Last intervention   168
 
Yes, I read it in full. A dropdown menu without VBA with a restriction if the casing is not respected is feasible.
0
Anthelm Posted messages 202 Status Member 1
 
My dropdown list is created from data validation and I have no way to enforce case sensitivity; perhaps I should create it in VBA instead...
0
Yoyo01000 Posted messages 1720 Registration date   Status Member Last intervention   168
 
In "Data validation," there is an "Error alert" tab that helps to address this!
0
Answer 3 / 3
Anthelm Posted messages 202 Status Member 1
 
Ok, the solution is with UCASE in my opinion.
This converts the result to uppercase.

If Worksheets("Options").Range("B2") = ("Yes")
the test is if = "Yes"
So if "yes", the result is false because of the missing uppercase.

But if we do:

If UCase(Worksheets("Options").Range("B2")) = ("YES")
In other words, if the result in uppercase = YES

No matter what the user enters: "yEs" becomes "YES" and it is equal to "YES", the result is true.
0