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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ShumsDistinguished 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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Subodh Tiwari (Neeraj)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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.