Change to Upper Case when a keystroke is made

[garypea123]

Hi,

Is there an automated VBA that will change the text in D4:D12, D22:D35 into upper case letters when a keystroke is made

Someone completes this file, and my OCD ways always want this in UPPER CASE.

Unless these is a simple way (without formulas)

Is

 

[Joe4]

Are you trying to make it upper case as they enter the data in column D, or are you trying to correct data that already exists in column D?
If you are tying to correct existing data, what action exactly should trigger this VBA code to run?

 

[garypea123]

Yes, its to correct what they have input and sent back to be (which comes in lower case)

It would have been great if there was some kind of custom code I could put into formatting cells, but I cannot see this.

In terms of change I guess its whenever they update any cell in the sheet (so as soon as they hit enter it will change anything to a capital letter in the cells I have indicated. It is not mandatory though, and if this is going to cause the sheet to malfunction or slow down I will not do this. I just have a little OCD with the small letters.

Hopefully that makes sense - Or maybe it updates the cells when the save button is pressed.

 

[Joe4]

OK, I have two options for you.

The first one is a data entry option, meaning this automatically makes the entry upper case as the enter the data (so it only works on new data entry, not existing data).
This code needs to go in the proper Worksheet module in VBA. The easiest way to ensure this is to go to your worksheet, and to right-click on the sheet tab name at the bottom of the screen and select "View Code". Then paste this VBA code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
    Set rng = Intersect(Target, Columns("D:D"))
   
'   Exit if no update made to column D entry
    If rng Is Nothing Then Exit Sub
   
'   Loop through each updated cell in column D
    Application.EnableEvents = False
    For Each cell In rng
        If cell <> "Product Code" Then cell = UCase(cell)
    Next cell
    Application.EnableEvents = True

End Sub

The other alternative is to use a "Before Save" script, that will update all existing data in column D.
This needs to be placed in the "ThisWorkbook" module in VBA.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim cell As Range

'   Made all values in column D upper case
    For Each cell In Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
        If cell <> "Product Code" Then cell = UCase(cell)
    Next cell

End Sub

Just don't try to use both at the same time!

 

[My Aswer Is This]

Try this:
This script will work for any cell in column D

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  12/17/2020  10:06:54 AM  EST
If Target.Column = 4 Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
End If
Application.EnableEvents = True

End Sub

 

[Joe4]

Try this:
This script will work for any cell in column D

Note that my first code will do that, but also ignore the entries of "Product Code", in case they are adding any more sections of that.

 

[garypea123]

Thats great, I have a couple of other projects I work on at the minute which is non upper casing priority - I will try over the next days, and provide some feed back...

But I understand the script, and positive it will work as I need

 

[footoo]

Or perhaps you could just format the cells with a capitals font.

 

[garypea123]

Okay, I will look at that too. I was looking for a customer code to use to do this, but I could not find anything. I will look in a bit more detail if its just a case of format cells (which would be better than macros')

 

[garypea123]

I cannot see anywhere on the internet where you can format the cells to turn small case into upper case automatically.

If it was available, then this would be the easiest thing.