Users have the flexibility to insert text in their preferred case. However, you might need the input to be only in uppercase. For such cases, you can use VBA code so that when the user enters a string, it automatically turns into uppercase. In this article, we will explore two examples of applying automatic uppercase in Excel VBA.
Introduction to Excel VBA UCase Function
VBA Ucase function is a built-in function that is used to convert text to uppercase. It is a text manipulation function that 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
To access the Microsoft Visual Basic window, go to the Developer tab and then click on Visual Basic. You can also open it by pressing Alt+F11 on your keyboard.
Then go to the Insert tab and click on Module to open the code Module.
Automatic Uppercase in Excel VBA: 2 Examples
In this article, we will demonstrate two examples to apply automatic uppercase in Excel VBA.
1. Make Automatic Uppercase for a Selected Range in Excel VBA
In this example, we will convert a range of names into uppercase. In the following dataset, we will capitalize all the letters of the names present in column B. The steps to achieve this goal are described below.
- First of all, select the range B5:B12 in your worksheet.
- Then go to VBA code Module and write 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
Code Breakdown
For Each myCell In myRange
    myCell.Value = UCase(myCell)
Next myCell
- This code starts a loop that iterated through each cell of the selected range.
- Then it converts each cell string into uppercase.
- Press F5 on your keyboard to run the code.
- As a result, all names present in the selected range will be converted into uppercase.
Read More: How to Change Lowercase to Uppercase in Excel
2. Make Automatic Uppercase in Excel VBA When Cell Value Changes
In this example, we will show you how you can convert text into uppercase when the cell value changes. A user might insert the cell values in different cases. However, we want these cell values to be converted into uppercase automatically when entered. The code for automatic uppercase in Excel VBA for cell changes is given below. Write this code in the worksheet code window.
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
Code Breakdown
Private Sub Worksheet_Change(ByVal Target As Range)- This is an event that is triggered when any cell of the worksheet is changed.
If Not Intersect(Target, rng) Is Nothing Then
        Application.EnableEvents = False
            Target.Value = UCase(Target.Value)
        Application.EnableEvents = True
    End If- This code first checks if the Target range intersects with the range called rng.
- If it does, then the code converts the values of cells in the Target range to uppercase.
Now write anything in column C in any case. Once you press Enter, the text will turn into uppercase.
Similar Readings
- How to Stop Auto Capitalization in Excel
- Excel VBA to Capitalize First Letter of Each Word
- How to Use VBA in Excel to Capitalize All Letters
- Excel Change to Proper Case without Formula
- How to Change Lowercase to Uppercase in Excel Without Formula
Excel VBA to Capitalize First Letter
In this example, we will use the Proper function in VBA to capitalize the first letter of each word (Propercase). We will use the following VBA code for this purpose.
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
Code Breakdown
For Each cell In selectedRange
cell.Value = Application.WorksheetFunction.Proper(cell.Value)
Next cell- This part converts each cell of the selected range into the proper case.
- Run the code by pressing F5 on your keyboard.
- As a result, an InputBox will pop up. Select the range you want to convert into proper case.
- Then click on OK. As a result, each word will have its first letter capitalized.
Read More: How to Make First Letter of Sentence Capital in Excel
Things to Remember
- In the Automatic Uppercase in Excel VBA for Cell Value Changes example, make sure to put the code in the worksheet Module.
- The Ucase function only works on lowercase text strings. It does not affect numbers, special characters, etc.
Download Practice Workbook
Download this practice workbook to exercise while reading this article.
Conclusion
Thanks for reading this article. I hope that you find the article helpful. In this article, we have demonstrated two examples of applying automatic uppercase in Excel VBA. We have discussed how to convert a whole range into uppercase as well as automatically convert text into uppercase when the cell values are changed. Moreover, we have covered how to use VBA to capitalize the first letter in each word. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.
