VBA Convert to uppercase in the on change function

[rplohocky]

Hello,
I'm looking for code to insert into the "on change" area that will convert lower case into upper case. The code would need to be for just column H. It should convert as soon as you leave the cell or another words after update. Thanks!

 

[Jonmo1]

Can you clarify the upper case.

Do you mean the entire contents should be upper case or just the first letter of a word?

hello there
should be
HELLO THERE
or
Hello There
?

 

[CodeNinja]

Something like this should do ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
    Application.EnableEvents = False
    Target = UCase(Target.Text)
    Application.EnableEvents = True
End If
End Sub

 

[CodeNinja]

If you want proper case like Jonmo1 suggested, the line should be Target = Application.WorksheetFunction.Proper(Target.Text)

 

[Jonmo1]

Here's another code, so it can handle multiple cells changing at once. And other errors

This capitalizes the whole cell.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, c As Range
Set MyRange = Intersect(Target, Range("H:H"))
If Not MyRange Is Nothing Then
    Application.EnableEvents = False
    On Error GoTo handlr
    For Each c In MyRange
        c.Value = UCase(c.Value)
    Next c
End If
handlr:
Application.EnableEvents = True
End Sub

As Codeninja said, change Ucase to Application.Proper if you want it the other way.

 

[rplohocky]

Something like this should do ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
    Application.EnableEvents = False
    Target = UCase(Target.Text)
    Application.EnableEvents = True
End If
End Sub

Thank you CodeNinja! That was what I was looking for. And thank you to everyone else for responding!