In this article, I am going to share a VBA code that can be used to change the cases of texts written in an excel worksheet. This can be used to enforce proper casing on texts written in a sheet or cell range. As you know – casing can be of 3 types: 1. lower, 2. UPPER or 3. Proper. Using this function you would be able to do any of this.

Using Excel formula

You can convert any text to lower, upper or proper cases in excel formula like below:

• =LOWER(F9)
• =UPPER(F9)
• =PROPER(F9)

Now let’s do this by VBA code. Using VBA code, you would be able to do it automatically without using any formula.

Change casing of Texts in a Worksheet

The following function will be run on the selected area on a worksheet and convert the texts into a cell in lower, upper or proper case.
Note: Following code will ignore those cells which already has formula in it. This is important so that you don’t mess with the formula in a cell.

Sub ChangeCase()
    Dim cellRange As Range
    Application.EnableEvents = False
    For Each cellRange In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
       cellRange.Value = StrConv(Rng.Text, vbUpperCase)
       'Similar to changing UpperCase, you can use 
       'vbLowerCase and vbProperCase for LowerCase and ProperCase respectively
    Next cellRange
    'enable vba events back again
    Application.EnableEvents = True
End Sub

You can also write a VBA code, which can change the case as soon as you type in a cell or cell range. This way, you can enforce user to type it a format you expect them to type. Incase they don’t type, VBA will automatically correct it in the correct format.

For example, in an Excel Form, if you want user to type their name in all Capital Letters, then you can use the following code in Worksheet_Change procedure. You are expected to type the Name in cell A10

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("A10"), Target) Is Nothing Then
        Application.EnableEvents = False
        If IsNumeric(Target.Value) = False Then
            Target.Value = StrConv(Target.Text, vbUpperCase)
        End If
        Application.EnableEvents = True
    End If
End Sub