Forcing CAP and Validate

Fun_Geek

New Member
Joined
Apr 3, 2002
Messages
7
I would like to force user data to CAPS on enter as well as validate to limit A, B, C, and D.

I can validate with drop down; but when drop dn list appears, user can enter lower case of above and enter (doesn't force Upper).

I tried using =EXACT(A1,UPPER(A1)) with Data Validation Custom which forces entry to Upper -but unable to limit to A, B, C, and D.

Ideas? TIA
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi
This will work, but there may be another way that I do not know of...

In Sheet Change Event
For Cell a1
Edit to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$1" Then
Target.Value = UCase(Target.Value)
Select Case Target.Value
Case "A", "B", "C", "D"
Application.EnableEvents = True
Exit Sub
Case Else
MsgBox "Invalid - Only A,B,C,D accepted"
Target.ClearContents
Target.Select
Application.EnableEvents = True
Exit Sub
End Select
End If
End Sub

Good Day!
Tom
 
Upvote 0
On 2002-04-04 01:36, Fun_Geek wrote:
I would like to force user data to CAPS on enter as well as validate to limit A, B, C, and D.

I can validate with drop down; but when drop dn list appears, user can enter lower case of above and enter (doesn't force Upper).

I tried using =EXACT(A1,UPPER(A1)) with Data Validation Custom which forces entry to Upper -but unable to limit to A, B, C, and D.

Ideas? TIA

Try:

=AND(EXACT(A1,UPPER(A1)),CODE(A1)>=65,CODE(A1)<=68)

Aladin
 
Upvote 0
Hi Mike


You maybe over complicating a simple issue. In the "Source" box of the Valdation just type:

A,B,C,D

They will not be able to enter lower case!
 
Upvote 0
Dave,

Used your methods as it sounded (and it was) the easiest. I was looking at some VBA work in a couple of other boards and here, but KISS always wins for me!

Thanks again...I have bookmarked and will be checking back often.

Mike
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top