VBA code to display or convert text to Upper Case using VBA UCase Function without using UPPER Function in each cell.
Usually, we need to allocate a separate column or manually enter Microsoft Excel Upper Function to display text in Upper Case. This makes the work hectic for us.
Moreover, this function can also be helpful when you create a template and want each alphabetical or alphanumeric entry to display the text in Upper Case.
For example, if you have an alphanumeric like “17Agpjps9630d1zi” using this code will display “17AGPJPS9630D1ZI” even if the entries are done in lowercase or proper case.
VBA code to display or convert text to Upper Case
To simplify the work, we have created a VBA code for you.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Private Sub Worksheet_Change(ByVal Target As Range) | |
| Dim Z As Long | |
| Dim xVal As String | |
| On Error Resume Next | |
| If Intersect(Target, Range("C8:C100")) Is Nothing Then Exit Sub | |
| Application.EnableEvents = False | |
| For Z = 1 To Target.Count | |
| If Target(Z).Value > 0 Then | |
| Target(Z).Value = UCase(Target(Z).Value) | |
| End If | |
| Next | |
| Application.EnableEvents = True | |
| End Sub |
Additionally, you can also check other VBA Codes SpellNumber Without Currency and SpellNumber for Indian Rupees. NUMBERTEXT and MONEYTEXT Function in OpenOffice Calc and SpellNumber Indian Rupees Function in Google Sheets.
How to Use VBA UCase Function To Convert Text in Upper Case
Follow the following steps to use this code in your excel worksheet.
Step 1
Open your new excel workbook in which you want to insert this VBA code.
Step 2
Navigate to the “Developer Tab”.
Step 3
Under the Code Tab, Click on “Visual Basic”.
Step 4
Double-click on the worksheet in which you want to insert this Code as shown in the image below:
Step 5
Copy and Paste the Code given above in the blank window on the right given for the VBA code.
Change the range based on your selection.
- If you want to define a complete column then change the range in the fifth column code Range(“A: A”)). I have selected here Column A, you can choose any and make changes accordingly.
- If you want to define a specific range then change the range like in the fifth column code Range(“A1:A25”)). This will convert the text from cell A1 to A25. You can define depending on your requirement.
Step 6
Save the file by clicking on the “Save” button in the top ribbon. A dialog box will appear as displayed in the image below:
Step 7
As our file consists of a macro thus you need to click the “No” option.
Step 8
The “Save As”window will open. Select Save As Type from the dropdown list as “Excel macro-enabled workbook” as shown in the image below:
Rename the file if you want to and else click the “Open” button.
Your file is ready with the Excel VBA UCase Function.
Please keep in mind that every time you open this workbook or any other macro-enabled workbook you will get a security warning below the ribbon. See the image below:
Click the button below to download the sample workbook.
We thank our readers for liking, sharing, and following us on different social media platforms.
If you have any queries or suggestions please share them in the comment section below. I will be more than happy to assist you.