Excel VBA to convert all characters to UPPERCASE

Need Excel VBA to force all cells to UPPERCASE even for cells that don't contain text only.
IO_DorkAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
All you need is this.
Right click on the Sheet Tab --> View Code --> and paste the following code into the opened code window --> Save your workbook as Macro-Enabled Workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Target = UCase(Target)
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Untested.
Dim cel As Range

For Each cel in Activesheet.usedrange.cells
    cel.value = ucase(cel.value)
Next

Open in new window

0
 
IO_DorkAuthor Commented:
I need something that runs automatically after I enter contents into a cell, not something I need to trigger with a shortcut or button.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
ShumsConnect With a Mentor Distinguished Expert - 2017Commented:
If you are referring just 1 cell then try add below code in Worksheet Code:
Private Sub WOrksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then 'change the cell reference here
    If Target = LCase(Target) Then
        Target = UCase(Target)
    End If
End If
End Sub

Open in new window

If you want to change number of cells then change the range from A1 to A1:A10 or whatever you need.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this tweaked code....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub

Open in new window

0
 
IO_DorkAuthor Commented:
Subodh - what is the difference in your tweaked code?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The proposed code is an Event code and in this case it is Sheet Change Event which gets triggered automatically when the content of the cell is changed.
So when the changes the case of the cell content and write it back to the same cell, the change event gets triggered again which is not required and sometimes it goes into infinite kind of loop. To avoid that, when the change event code triggers first time, the Events are disabled by the line Application.EnableEvents = False and set to True again in the end of the code.

Hope that helps.
1
 
IO_DorkAuthor Commented:
Thanks boss!
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You'r welcome!

Please read the following line in second paragraph...
So when the changes the case of the cell content and write it back to the same cell,

AS
So when the code changes the case of the cell content and write it back to the same cell,

:)
0
 
Rob HensonFinance AnalystCommented:
Will this affect entering formulas as well?

Presumably formula will get converted to its resulting value in upper case where relevant.
0
All Courses

From novice to tech pro — start learning today.