Applying Automatic Uppercase in Excel VBA – 2 Examples

Introduction to Excel VBA UCase Function

VBA Ucase function is a built-in function used to convert text to uppercase. It takes strings as input and converts each letter of the string to uppercase.

Syntax

Ucase(String)

The String can be a text string or a variable that contains a text string or a range containing text strings.

How to Launch VBA Editor in Excel

Go to the Developer tab and click Visual Basic. You can also open it by pressing Alt+F11.

Go to the Insert tab and click Module to open the code Module.

Example 1 – Apply Automatic Uppercase to a Selected Range in Excel VBA

To capitalize all the letters  in column B:

• Select B5:B12.

• Go to the VBA code Module and enter the following code.

Sub UpperCaseRange()
Dim myRange As Range
Set myRange = Selection
For Each myCell In myRange
    myCell.Value = UCase(myCell)
Next myCell
End Sub

For Each myCell In myRange
    myCell.Value = UCase(myCell)
Next myCell

Example 2 – Apply Automatic Uppercase When a Cell Value Changes in Excel VBA 

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("C:C")
If Not Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, rng) Is Nothing Then
        Application.EnableEvents = False
            Target.Value = UCase(Target.Value)
        Application.EnableEvents = True
    End If

Read More: How to Use VBA in Excel to Capitalize All Letters

Using Excel VBA to Capitalize the First Letter

Use the Proper function in VBA to capitalize the first letter of each word (Propercase). Enter the following VBA code:

Sub VBACapitalizeFirstLetter()
Dim selectedRange As Range
Set selectedRange = Application.Selection
Set selectedRange = Application.InputBox("Select Range", _
"Capitalize Each Word", selectedRange.Address, Type:=8)
Dim cell As Range
For Each cell In selectedRange
cell.Value = Application.WorksheetFunction.Proper(cell.Value)
Next cell
End Sub

For Each cell In selectedRange
cell.Value = Application.WorksheetFunction.Proper(cell.Value)
Next cell

• Click OK.

First letters are capitalized.

Read More: Excel VBA to Capitalize First Letter of Each Word

Things to Remember

• Use the Ucase function for lowercase text strings only.

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Stop Auto Capitalization in Excel
• How to Change Case in Excel Without a Formula
• How to Change to Title Case in Excel

<< Go Back to Change Case | Text Formatting | Learn Excel