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

Option Explicit

Private WithEvents App As Application

Private Sub Class_Initialize()
Set App = Application
End Sub

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
With Target
If Not .HasFormula Then
Application.EnableEvents = False
On Error Resume Next
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
End If

End Sub

e. open the subfolder called Microsoft Excel Objects and double click on ThisWorkbook

f. paste the code below into the ThisWorkbook code

Option Explicit

Private OurEventHandler As CAppEventHandler

Private Sub Workbook_Open()
Set OurEventHandler = New CAppEventHandler
End Sub

This works for any sheet in the given range.