Automatic Uppercase in Excel VBA (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

1. Go to Microsoft Visual Basic Application

Then go to the Insert tab and click on Module to open the code Module.

Insert a code Module in VBA


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.

dataset for Automatic Uppercase for a Selected Range in Excel VBA

  • 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 for Automatic Uppercase for a Selected Range in Excel VBA

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.

Automatic Uppercase for a Selected Range in Excel VBA


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

Automatic Uppercase in Excel VBA for Cell Value Changes

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.

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

 


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

VBA Capitalize First Letter

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.

VBA Capitalize First Letter

  • Then click on OK. As a result, each word will have its first letter capitalized.

VBA Capitalize First Letter

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


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alif Bin Hussain
Alif Bin Hussain

Alif Bin Hussain earned a BSc in Civil Engineering from Bangladesh University of Engineering and Technology. As an engineering graduate, he has a deep passion for research and innovation. He loves to play with Excel. In his role as an Excel & VBA Content Developer at ExcelDemy, he not only solves tricky problems but also shows enthusiasm and expertise in handling tough situations with finesse, emphasizing his dedication to delivering top-notch content. He is interested in C, C++,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo