Forcing CAP and Validate

Fun_Geek · Apr 4th, 2002, 02:36 AM

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

Tom Schreiner · Apr 4th, 2002, 03:14 AM

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

Aladin Akyurek · Apr 4th, 2002, 04:14 AM

Quote:

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

Dave Hawley · Apr 4th, 2002, 05:00 AM

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!

Fun_Geek · Apr 4th, 2002, 12:15 PM

Thanks! Solution has worked...you guys are the greatest!!!

Mike

Dave Hawley · Apr 4th, 2002, 07:19 PM

Hi Mike

Just curiuos which method you used?

Fun_Geek · Apr 4th, 2002, 11:34 PM

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

Dave Hawley · Apr 5th, 2002, 12:42 AM

Hi Mike

I couldn't agree more!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 4th, 2002, 02:36 AM	#1
Fun_Geek New Member Join Date: Apr 2002 Posts: 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

Apr 4th, 2002, 03:14 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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

Apr 4th, 2002, 05:00 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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!

Apr 4th, 2002, 12:15 PM	#5
Fun_Geek New Member Join Date: Apr 2002 Posts: 7	Thanks! Solution has worked...you guys are the greatest!!! Mike

Apr 4th, 2002, 07:19 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Mike Just curiuos which method you used?

Apr 4th, 2002, 11:34 PM	#7
Fun_Geek New Member Join Date: Apr 2002 Posts: 7	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

Apr 5th, 2002, 12:42 AM	#8
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Mike I couldn't agree more!