Excel – Force Uppercase Text in a Column
Forcing uppercase text in a column is sometimes useful if multiple users work on the same file and you want to make this condition by default.
There are 2 ways to do this:
1. Non-VBA Method
Special requirements: None
Limitations: This is indirect, meaning that it will not allow you to insert text other than UPPERCASE, showing an error. You can insert numbers, no problem, but at long as there’s a lowercase letter included, it will show an error.
a. select the desired range
b. go to the Data ribbon, click on Data Validation
c. Allow: Custom: =EXACT(UPPER(A2);A2)
d. Replace A2 with the first cell of your desired range
e. click the Error Alert tab and edit an error message in case lower text is inserted in the range
f. click OK and test
2. VBA Method
Special requirements: Creating a PERSONAL.XLSB file if you don’t have one. See THIS GUIDE to do that. Must be done only once.
Limitations: None that I know of.
a. open VBA by going to the Developer ribbon and clicking on Visual Basic
b. right click VBAProject(PERSONAL.XLSB) and choose Insert – Class Module
c. in the Properties window (if not showing: View – Properties Window) rename the Class Module to CAppEventHandler
d. paste the code below into the Class Module and change the target range to the one you need and the file name to the one you want the macro to apply to
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveWorkbook.Name = “Book1.xlsx” Then
If Not (Application.Intersect(Target, Range(“A1:A2000“)) Is Nothing) Then
If Not .HasFormula Then
Application.EnableEvents = False
On Error Resume Next
.Value = UCase(.Value)
Application.EnableEvents = True
e. open the subfolder called Microsoft Excel Objects and double click on ThisWorkbook
f. paste the code below into the ThisWorkbook code
Private OurEventHandler As CAppEventHandler
Private Sub Workbook_Open()
Set OurEventHandler = New CAppEventHandler
This works for any sheet in the given range.