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.

 

[SimonHughes]

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.

Hi Joe,

The range is C10 to AG20 on 12 sheets (January through to December). I guess I would need to paste the code into all sheets?

 

[SimonHughes]

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

Hi Efan, thanks for the response, I am going to see what Joe has said in reply to my answer,

 

[EFANYoutube]

Hi Efan, thanks for the response, I am going to see what Joe has said in reply to my answer,

Go with Joe mate

 

[SimonHughes]

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

Hi Joe, Job done, I changed the range to suit and pasted it into each sheet individually. Interested to see if there is a whole workbook function though - many thanks.

 

[Joe4]

Hi Joe, Job done, I changed the range to suit and pasted it into each sheet individually. Interested to see if there is a whole workbook function though - many thanks.

Yes, if you want the same thing to happen in every sheet, there is a way to just add the code in one place.

The code needs to be placed in the "ThisWorkbook" module in VBA, as shown here (open the VB Project Explorer if you do not see this):

And then we just need to make a few slight adjustments to the code, so it looks like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Sh.Range("C10:AG20"))
    
    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

That should do what you want to every sheet in your workbook.

 

[SimonHughes]

Thats great Joe, thanks for yiur help, appreciated.

 

[Joe4]

You are welcome.
Glad we were able to help!