Data validation for Capital Letter

  • Thread starter Thread starter gilbert
  • Start date Start date
G

gilbert

Just wonder if we can use Data Validation to auto convert the tex
entered into Capital Letters if small letters are used? I don't want t
prompt any error message on small letters used but want Excel to aut
recognize and convert the same into Capital Letters.

Please advise if you know.

Thank you

Best regards,
Gilber
 
Validation can't change the cell values - only reject invalid ones.

You could use a worksheet event macro. Put this in the worksheet code
module (right-click on the worksheet tab and choose view code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A1:A10, J1:J10")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Text)
Application.EnableEvents = True
End If
End With
End Sub

This will change text entered in A1:A10 or J1:J10 to upper case.

To convert to Proper Case, change

.Value = UCase(.Text)

to

.Value = Application.Proper(.Text)
 
Back
Top