Always upper case

[SimonHughes]

Hello, is it possible to format cells so the result is always upper case even if entered as lower case?

I am using MS365, TIA.

 

[Joe4]

Sure. But it requires VBA.

If you are open to VBA, just let us know what range you want to apply it to, and we can give you the code for that.

 

[EFANYoutube]

Is there a particular column, or is it the whole work sheet?

 

[EFANYoutube]

This code should do the whole sheet even if the data is pasted in bulk from somewhere else

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Target
    cell.Value = UCase(cell.Value)
Next cell
End Sub

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor that opens (the code MUST be placed in this Sheet module in order to work automatically).

This will make everything in columns A-C upper case, though the range can be changed to work for any range you want.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("A:C"))
    
    If rng Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    For Each cell In rng
        cell.Value = UCase(cell.Value)
    Next cell
    
    Application.EnableEvents = True
        
End Sub

 

[Fluff]

@EFANYoutube that code is going to end up in a permanent loop.

 

[EFANYoutube]

@EFANYoutube that code is going to end up in a permanent loop.

Please let me know how so I can learn as I have tested it but must be something I'm not seeing

 

[Fluff]

Try putting a break point in the code & when it stops use F8 to step through the code.

 

[Joe4]

Please let me know how so I can learn as I have tested it but must be something I'm not seeing

You need to be very careful with Worksheet_Change event procedures. They fire when a cell is being updated. But if your code is updating a cell, the code will call itself. Hence, you can get caught in an infinite loop.

The way to avoid that is to temporarily disable events from firing while your code is making changes. like I did. You can do that by adding this line prior to making the changes:

    Application.EnableEvents = False

Then, after all changes are complete, you need to turn it back on again, or else this automated code will stop working in your Excel session.

    Application.EnableEvents = True

 

[EFANYoutube]

Try putting a break point in the code & when it stops use F8 to step through the code.

Great! thanks, I forgot the .EnableEvents = False