VBA to make a cell always uppercase

[smking204]

I'm familiar with recording macros, and I've dabbled with manually editing macros.

Now what I want is something different: a script that will always convert the contents of a cell (B3 in this case) to uppercase. Been googling it, but all I can find are different versions of creating a macro, i.e., something that I need to run by clicking a button, etc. But I want the cell effectively permanently formatted, as soon as the text is entered. I think this means NOT using a subroutine ("sub" / "End sub") but I'm not immersed enough in coding to know if that's a key part of this or not. Thanks in advance!

 

[Eric W]

You will need a macro, but a specialized one called an event handler. It triggers when a specific event occurs, in this case when the value of B3 changes. To try it, open a copy of your workbook. On the sheet with the cell you want monitored, right click the sheet tab and select View Code. In the window that opens, paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$3" Then
        Application.EnableEvents = False
        Target.Value = UCase(Target.Value)
        Application.EnableEvents = True
    End If
   
End Sub

Return to Excel and try it.

 

[smking204]

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then Application.EnableEvents = False Target.Value = UCase(Target.Value) Application.EnableEvents = True End If End Sub

ha, yep. That does it. That's more code than I thought it would take. Maybe I can parse through this to understand it better. Thanks!

 

[Joe4]

ha, yep. That does it. That's more code than I thought it would take. Maybe I can parse through this to understand it better. Thanks!

That is really very short, succinct code. There really isn't much going on.

The first line if just checking the cell address to limit it to run just against B53.
This is the only line making the update, converting the new entry to all upper case:

Target.Value = UCase(Target.Value)

The other two rows are important rows that you often must use when creating "Worksheet_Change" event procedure code.
"Worksheet_Change" procedure code is VBA code that runs automatically when cells are manually changed (when I saw manually, I mean not by formula or link, but rather hard-coded).
However, since your code itself is making a change to the cell, that change would call the code again (the code would be calling itself)!
If not careful, you can get caught in an infinite loop and crash your system (as every change calls the code to run again!).

To prevent that from happening, you add a line like this just before the row that makes the change:

Application.EnableEvents = False

That will temporarily disable the automated events from running (so changes would call the code to run again).

Then, after the change is complete, you will want to turn events back on, so the code can run again in the future should another change happen later on.
So you add this code:

Application.EnableEvents = True

Hope that help clarifies what Eric's code is doing.