modify code to make all words Proper case , not just all Upper case

[steve400243]

Hello, I use this code to make all letters capitol letters, Need to modify it so that it changes all words to proper case. I cannot figure it out with multiple google searches.

For instance HAPPY DAYS to Happy Days. Thanks for your help forum Experts.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell   As Range
    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
        cell = vbProperCase(cell)
    Next
    Application.EnableEvents = True
End Sub


Sub RunOnce()
Dim c As Range
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
    c = vbProperCase(c)
Next
Application.ScreenUpdating = True
End Sub

 

[Norie]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
        cell.Value = StrConv(cell.Value, vbProperCase)
    Next
    Application.EnableEvents = True

End Sub

 

[steve400243]

That did it, thank you for looking at it Norie!

For Each cell In Target
cell.Value = StrConv(cell.Value, vbProperCase)

 

[steve400243]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
        cell.Value = StrConv(cell.Value, vbProperCase)
    Next
    Application.EnableEvents = True

End Sub

would there be a way to exclude column B?

 

[Joe4]

would there be a way to exclude column B?

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
         If cell.Column <> 2 Then
             cell.Value = StrConv(cell.Value, vbProperCase)
         End If
    Next
    Application.EnableEvents = True

End Sub

 

[steve400243]

Thank you for the quick Answer Joe4. Much appreciated.

 

[Rick Rothstein]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
        cell.Value = StrConv(cell.Value, vbProperCase)
    Next
    Application.EnableEvents = True

End Sub

I would recommend using Excel's PROPER function instead...

cell.Value = Application.Proper(cell.Value)

as it handles the proper casing of text better. One example (there are a couple of others which I don't remember offhand) would be (note the first letter after the left parenthesis)...

one two (three) four

StrConv will return...

One Two (three) Four

whereas Excel's PROPER function will return...

One Two (Three) Four

 

[steve400243]

Thank you Rick, I appreciate the information. So much to learn.

 

[steve400243]

Hi Rick, This is not working for the additional code to exclude columnB?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell   As Range
    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
    If cell.Column <> 2 Then
    cell.Value = StrConv(cell.Value, vbProperCase)
    Next
    Application.EnableEvents = True
End Sub

 

[Rick Rothstein]

Hi Rick, This is not working for the additional code to exclude columnB?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell   As Range
    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
      If cell.Column <> 2 Then
        cell.Value = StrConv(cell.Value, vbProperCase)
      [B][COLOR="#FF0000"]End If[/COLOR][/B]
    Next
    Application.EnableEvents = True
End Sub

It is always a good idea to include the error number and error description when your code is generating an error. I also find full indenting of code makes it easier to spot the problem. In your case, you were missing the End If statement that I show in red above. I notice you did not take my suggestion and replace the StrConv function with Excel's PROPER function. While it is your choice whether to do so or not, of course, but I really would recommend making the change.