Excel Scripts to Validate Cells and Force Upper Case

In the attached spreadsheet, in the tab labeled "AB," you can see that for columns 6-8 and columns 11-15 I have validation turned on which allows only a single letter to be entered.  The validation also forces a user to enter that letter in upper case.  Technically this all works well, but it's a bit of a pain for the user to always hold down SHIFT when they want to type a capitol letter in one of those validated fields.

I would like to make it easier on the user, so a lower case letter can be entered and have it turn into an upper case.  So ideally the scripts would do these things:

For column6, validate so only "C" can be entered, but accept a lower case letter to be typed and then force it to upper.

For column7, validate so only "B" can be entered, but accept a lower case letter to be typed and then force it to upper.

For column8, validate so only "P" can be entered, but accept a lower case letter to be typed and then force it to upper.

For columns 11-15, validate so only "Y" can be entered, but accept a lower case letter to be typed and then force it to upper.

I'm sure that if someone could help me with just one of those, I could modify it to work with the other columns.
Copy-of-28736107.xlsm
witzph1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

witzph1Author Commented:
One other thing I would like is that the script be written in such a way as to work on any sheet.  That would allow me to copy the tab, rename it, and still have the script work.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Place the following code on your AB Sheet Module.

I have removed the data validation from all the target columns.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row > 6 Then
Application.EnableEvents = False
    Select Case Target.Column
        Case 3
            If Target = "c" Or Target = "C" Then
                Target = UCase(Target)
            Else
                Target = ""
            End If
        Case 4
            If Target = "b" Or Target = "B" Then
                Target = UCase(Target)
            Else
                Target = ""
            End If
        Case 5
            If Target = "p" Or Target = "P" Then
                Target = UCase(Target)
            Else
                Target = ""
            End If
        Case 8, 9, 10, 11, 12
            If Target = "y" Or Target = "Y" Then
                Target = UCase(Target)
            Else
                Target = ""
            End If
    End Select
Application.EnableEvents = True
End If
End Sub

Open in new window

For detail, please refer to the attached workbook.
28736107.xlsm
0
witzph1Author Commented:
sktneer, I pasted your script into the AB worksheet code just below the other script which begins

Private Sub Worksheet_Activate()

When I enter a lower case 'c' in column6 I am still getting the validation that says I must enter an upper case C which I believe is coming from my validation.  I'm not getting any script errors.  Perhaps I pasted the script in the wrong place?  

I've reattached the modified spreadsheet so you can see what I have so far.
Copy-of-28736107.xlsm
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Martin LissOlder than dirtCommented:
You don't need any additional code. Just change your Data Validation to, for example, C, c. After c is entered it will be capitalized.
0
Martin LissOlder than dirtCommented:
Actually you can just make it lower case c and it will work the same.
0
witzph1Author Commented:
Got it!  That did the trick.  I changed my validation to be just 'c' and it worked.

I thought when you said "I have removed the data validation from all the target columns" that programmatically you had turned off my validations and your script was now doing both the validation and the upper case.
0
Martin LissOlder than dirtCommented:
When I said "I have removed the data validation from all the target columns" I believe I was referring at that time to just the columns that had required content.
0
witzph1Author Commented:
Thank you so much, Martin!  Much appreciated!
0
Martin LissOlder than dirtCommented:
You chose sktneer's answer rather than mine. If you want to change that then Request attention and ask for a moderator's help.
0
witzph1Author Commented:
Oh my goodness.  I totally didn't catch the fact that a different person had jumped in.  No wonder I was confused.  I was trying to put your comments together with sktneer's.  

Now that I understand the two comments were totally unrelated, I now see that you are saying I don't need a script to accomplish getting upper case.  I believe what you are saying is that by changing my validation to just accept 'y' it should change to upper.  But I'm not finding that to be the case.  If I have validation to Y or y it accepts either and doesn't change lower to upper.  If my validation is set to 'y' it only accepts lower case y and not upper.

So the success I had was mostly through sktneer's script.
0
Martin LissOlder than dirtCommented:
OK, sorry for the confusion.
0
witzph1Author Commented:
sktneer:

I have a couple questions about your script, and one slight modification to my needs.  

First, the modification.  I had previously said "For columns 11-15, validate so only "Y" can be entered, but accept a lower case letter to be typed and then force it to upper."  But I now only need columns 11-14 to validate to "Y", and in fact the last column, column 14 needs to allow either a "Y" or a "N".

Then, my questions.  You said "I have removed the data validation from all the target columns" and the line in your script which reads:

Application.EnableEvents = False

...seems to be the one which would disable my validation rules.  But when I enter a lower case letter in one of the columns my validation rules seem to still be popping up for the user to see.   Do I have something setup wrong?  

I've reattached the file here with the one column taken out.
Copy-of-Copy-of-28736107.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
No actually I cleared the data validation from your sheet. I don't think that it is required once you are using a VBA code to do that. So both the two methods were checking the cell input with only difference that the VBA code was converting the letter case if the correct value in entered in the cell.

I have removed all the data validation rules from the sheet and added them into the VBA code so that user will get an error message like data validation in case of invalid input in the cell.

You may edit the msg used in the code as per your choice.
For detail refer to the attached workbook.
8736107.xlsm
0
witzph1Author Commented:
Oh, that is perfect.  Very, very nice.  One last little question. Is it possible to modify the script in such a way that when someone deletes a value from one of the fields being validated by the script that it doesn't pop up the validation message?  When someone deletes a value, they get the validation message.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Here it is....
8736107.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
witzph1Author Commented:
Awesome!  Thank you SO MUCH!

For those who may read this case later, the script sktneer posted earlier was modified in the attached file.  You can see it in the "AB" tab code.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.