VBA To Capitalize First Letter Of Every Word In Cell

[data808]

I would like to be able to type into columns D and E and have the first letter for each word be capitalized for every cell.

Does anyone have the VBA code to do this?

Thank you for your help on this.

 

[kevin9999]

Please try the following worksheet change code for the sheet in question. Right-click the sheet tab, select View Code & paste the code into the window that appears on the right of screen.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("D:E"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Target.Value = WorksheetFunction.Proper(Target.Value)
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[Michael M]

Maybe this way

Sub MM1()
Dim rng As Range, lr As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row
For Each rng In Range("D2:E" & lr)
rng.Value = StrConv(rng.Value, vbProperCase)
Next rng
End Sub

 

[data808]

Please try the following worksheet change code for the sheet in question. Right-click the sheet tab, select View Code & paste the code into the window that appears on the right of screen.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("D:E"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Target.Value = WorksheetFunction.Proper(Target.Value)
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Thank you very much. This is working great!

 

[data808]

Maybe this way

Sub MM1()
Dim rng As Range, lr As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row
For Each rng In Range("D2:E" & lr)
rng.Value = StrConv(rng.Value, vbProperCase)
Next rng
End Sub

Thank you for the attempt. Couldn't get this one to work.

 

[Rick Rothstein]

Maybe this way

Sub MM1()
Dim rng As Range, lr As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row
For Each rng In Range("D2:E" & lr)
rng.Value = StrConv(rng.Value, vbProperCase)
Next rng
End Sub

The StrConv version of making a text string proper case is not as good as calling Excel's PROPER function through the Worksheefunction object (as kevin9999 did). Try both on the following text string to see the difference...
"one-two(three)"

 

[Michael M]

Thanks Rick.

 

[kevin9999]

Thank you very much. This is working great!

Glad we could help, and thanks for the feedback