Force Text to Upper- or Proper Case
Restrict all future entries in a spreadsheet or range of cells to uppercase or proper case only.
Rather than convert the existing contents of a spreadsheet to either uppercase or proper case, you can restrict all future entries to either uppercase or proper case for a range of cells or an entire spreadsheet.
With some VBA code in the Private module of the Worksheet object, you can force any text entered to be uppercase (all caps), or proper case (capitalizing the first letter of each word). This tutorial presents four Excel VBA procedures that will do the trick. The first two restrict the forcing of uppercase to a specified range on the worksheet, and then the entire worksheet. The last two procedures do the same but force text to be entered as proper case.
The Code
To insert one of the procedures, right click on the sheet tab, select View Code, and enter the procedure you require.
It is very important that you insert one and one only procedure to prevent erroneous results.
Uppercase. If you want to force all future text in your worksheet to uppercase, but restrict the range, you can use the following procedure, which will force uppercase text in cells A1:B20 only:
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to UPPER case for the range A1:B20
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub
If, however, you want to force uppercase entry throughout your worksheet, use this procedure instead:
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces all text to UPPER case
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
On Error GoTo 0
End Sub
Exit the VBE and save your workbook.
Now, any text that you try to enter into the textbox will be forced to either upper or proper case.
This code can also be used in a textbox on a userform.