Validation for text

  • Thread starter Thread starter Pinda
  • Start date Start date
P

Pinda

I want to validate a cell so that it will always enter
capital letters even if caps lock is off, for ease of
entry.

Is it possible?

Regards.
 
Thanks alot mate thats brilliant!!

Is ther a funtion for Sentence case as opposed to UCase,
where john would enter John?

Cheers mate, i'm learning sooo much for this forum!!
 
Just change the line .Value = UCase(.Value) to .Value =
Application.Proper(.Value)

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Hi Paul,

Thanks for that.

I tried both but they don't seem to work. I pasted the
whole code of the caps 1, changed the name of the private
sub and gave it the relevant cell ref but it goesn't
change.

any ideas?

Thanks a bunch.
 
Pinda, this should work, do not change the name of the sub, only change the
cell ref.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Error_handler
'change to the cell or cells you want below
If Not Intersect(Range("A1,B1"), Target) Is Nothing Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(Left(.Value, 1)) & Right(.Value,
Len(.Value) - 1)

End If
End With
End If

Error_handler:
Resume Next
Application.EnableEvents = True
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Hi,

If I leave the name of the sub the same (Worksheet_Change)
I get an error message saying 'Compile error, Ambiguous
name detected: Worksheet_Change)

Note this sub is directly under the UCaps sub, dunno if
that helps.

Thanks.
 
Pinda, you can only have one worksheet change sub in a sheet, if you have
one for caps and one for Sentence case excel would not know which one to run
 
Back
Top