Prev: Delete the contents of a cell if it contains #DIV/0! Automatic
Next: Blank Page prints after last info?
From: juanpablo on 27 Apr 2010 11:07
How do I restrict some columns of having all text in UPPER CASE, some columns
to have text only First In Capital Letter and some columns only in lower
case??

Thanks

JPG
From: Gord Dibben on 27 Apr 2010 13:15
You can restrict using Data Validation but I would find that very annoying.

Example............in DV>Allow>Custom =EXACT(A1,UPPER(A1)) will not allow
anything but Uppercase.

I would use event code to automatically change Case when anything is
entered.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
If .Column = 1 Then
.Formula = UCase(.Formula)
End If
If .Column = 2 Then
.Formula = LCase(.Formula)
End If
If .Column = 3 Then
.Formula = Application.Proper(.Formula)
End If
End With
ErrHandler:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.

Edit column numbers to suit.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP

On Tue, 27 Apr 2010 08:07:01 -0700, juanpablo
<juanpablo(a)discussions.microsoft.com> wrote:

>How do I restrict some columns of having all text in UPPER CASE, some columns
>to have text only First In Capital Letter and some columns only in lower
>case??
>
>Thanks
>
>JPG

 | 
Pages: 1
Prev: Delete the contents of a cell if it contains #DIV/0! Automatic
Next: Blank Page prints after last info?