Can I force a cell to Proper Case if is already using Conditional Formating and Data Validation?

[USAMax]

My client wants, "Yes" or "No" in proper case so if someone enters one of them in upper or lower case the Data Validation gives them an error. Can this be changed to proper case before Data Validation kicks in?

I would rather not do this in a macro if there is another way.

Thank you,

 

[anglais428]

If you get the user to enter in a different cell you can use =PROPER()

 

[USAMax]

Thank you Anglais,

The worksheet is 178 columns with extensive Data Verification, Conditional Formatting and formulas. The program hides and unhides the columns based on several variables so the user only sees what they have to but in the end all columns are unhidden and the extra column would be confusing. I have a lot of Yes/No Data Verified cells and I would like the user to be able to type their answer without having to worry about Case.

I thought you could adjust case using, "Format Cells"

 

[anglais428]

Perhaps if you created a list (I.e. create a table of combinations) then reference that table in the data validation? The user would then be able to write YES, Yes, yes, NO, No, no.

 

[USAMax]

This list would be a very good solution when they type their answer but confusing when they use the drop-down.

I found in, "Format Cells" I can go to Custom and type =Proper() and it will properly change the case but as it checks the Data Validation first before changing the case it is not the solution.

 

[shg]

You could use custom validation: =OR(EXACT(Me, "Yes"), EXACT(Me, "No"))

Replace Me with the address of the cell where it is used.

 

[Rick Rothstein]

Thank you Anglais,

The worksheet is 178 columns with extensive Data Verification, Conditional Formatting and formulas. The program hides and unhides the columns based on several variables so the user only sees what they have to but in the end all columns are unhidden and the extra column would be confusing. I have a lot of Yes/No Data Verified cells and I would like the user to be able to type their answer without having to worry about Case.

I thought you could adjust case using, "Format Cells"

No, unfortunately, Excel is rather weak on formatting text via Cell Formatting. There is a VB event code solution....

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Value = "yes" Or Target.Value = "no" Then
    Application.EnableEvents = False
    Target.Value = StrConv(Target.Value, vbProperCase)
    Application.EnableEvents = True
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm).

 

[USAMax]

Thank you shg,

I entered the following code into the Data Validation:

OR(Exact(SNCODE.YesNo, "Yes"), Exact(SNCODE.YesNo, "No"))

In the drop-down window I get two options
OR(Exact(SNCODE.YesNo, "Yes")
Exact(SNCODE.YesNo, "No"))

If I add the equals sign before it I get the error:
"The list source must be a delimited list, or a reference to single row or column."

 

[USAMax]

Thanks Rick, you have always come through for me. Unfortunately this client has a lot of Data Validation and they want it to work just like all the others, with a drop-down but they also want their client to be able to type the answer or have Excel "Type Ahead". I already have the Worksheet_Change setup and I could just plug this in but it is not what they want.

You are amazing Rick you must help a lot of people considering how much you have helped me. Thank you!

 

[USAMax]

I just changed the Data Validation to Custom instead of List and it accepted the code with the equals sign but it is not working as I can enter anything plus there is no drop-down list.