Restrictions in format

  • Thread starter Thread starter juanpablo
  • Start date Start date
J

juanpablo

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
 
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
 
Back
Top