Forcing text in a cell to Upper Case

How do you force entries to UPPER CASE in an Excel Cell. In the attached spreadsheet I tested tested the VBA code below referencing cells F4:G28. In total, I want to force cells ranges F4:G28, J4:K28, N4:V28, F34:G58, J34:K58 and N34:V58. I do not want to do Data Validation, just a conversion to Upper Case, no matter what someone enters. I the VBA code below which I found in EE's Knowledge Base, but it doesn't seem to work unless you go back into the cell re-enter the first letter of the initial entry.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Target, Range("F4:G28")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If
End Sub
JTL-Log-17126-17150-EE5.xlsm
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?
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.

 
Wayne Taylor (webtubbs)Commented:
Because it's the Worksheet_Change() event, it doesn't do anything unless a cell in the specified range changes, and even then it will only apply UPPER to the cell that changes. The code you have will be fine going forward, but to start with, use the below code to convert all current entries to UPPER...


Sub ConvertToUpper()

    Application.EnableEvents = False
    Dim cell As Range
    For Each cell In Range("F4:G28")
        If Not cell.HasFormula Then
            cell.Value = UCase(cell.Value)
        End If
    Next
    Application.EnableEvents = True
    
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
I would modify Wayne's code so that it does all the ranges at once.

Sub ConvertToUpper()

    Application.EnableEvents = False
    Dim cell As Range
    Dim rng As Range
    
    Set rng = Union(Range("F4:G28"), Range("J4:K28"), Range("N4:V28"), _
                    Range("F34:G58"), Range("J34:K58"), Range("N34:V58"))
    For Each cell In rng
        If Not cell.HasFormula Then
            cell.Value = UCase(cell.Value)
        End If
    Next
    Application.EnableEvents = True
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

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
 
Wayne Taylor (webtubbs)Commented:
Yes, I used only the range in the code example. For non-contiguous ranges, I would do it like this...

Sub ConvertToUpper()

    Application.EnableEvents = False
    Dim cell As Range
    For Each cell In Range("F4:G28, J4:K28, N4:V28, F34:G58, J34:K58, N34:V58")
        If Not cell.HasFormula Then
            cell.Value = UCase(cell.Value)
        End If
    Next
    Application.EnableEvents = True
    
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Bill GoldenExecutive Managing MemberAuthor Commented:
Testing
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
Thanks for the help guys.
0
 
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.