Applying Automatic Uppercase in Excel VBA – 2 Examples

The 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