Make First Letter of Each Worh in a Cell to Upper Case

[Denny57]

I have successfully applied vba code to a text box in a User Form which will convert the first letter of each word to upper case using "vbProperCase".

Private Sub Textbox1_Change()
' Converts first letter of each work in the text to Upper case

With Me.ActiveControl
.Value = StrConv(.Value, vbProperCase)
End With

End Sub

I am looking to apply a Private Sub to a specific worksheet to convert all text input into 2 columns (B and J in rows 5 thru 70).

I have tried to adjust the above code however I keep getting error messages whatever change I make.

Any help would be appreciated

 

[johnnyL]

How about:

Sub CapitalizeFirstLetterOfWordsInSomeRanges()
'
    Range("B5:B70").Value = Application.Proper(Range("B5:B70").Value)
    Range("J5:J70").Value = Application.Proper(Range("J5:J70").Value)
End Sub

 

[Denny57]

I have copied the code to the appropriate worksheet as I have done for other worksheet specific code within a workbook but the first characters remain as lower case.

Macros are enabled and the file is saved as .xlsm

 

[Alex Blakenburg]

It sounds like you want a worksheet event. See if this is the sort of thing you are after.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngProper As Range
    Dim rngUpdate As Range
    
    Application.EnableEvents = False

    Set rngProper = Range("B5:B70,J5:J70")
    Set rngUpdate = Intersect(Target, rngProper)

    If Not rngUpdate Is Nothing Then
        rngUpdate.Value = Application.Proper(rngUpdate.Value)
    End If

    Application.EnableEvents = True

End Sub

 

[johnnyL]

I have copied the code to the appropriate worksheet as I have done for other worksheet specific code within a workbook but the first characters remain as lower case.

Macros are enabled and the file is saved as .xlsm

Sorry @Denny57, I didn't realize you wanted it to automatically run. The code I provided was designed to be run manually when you wanted it to run.