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

 

[steve400243]

Thank you again Rick, I have it running with your code now, and it works great. I really appreciate the tips! Have a nice Day

 

[steve400243]

Proper case code not working correctly

Hello, I keep getting an error code with code to make everything Proper case? Any ideas. I need it to exclude Column A and B

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 <> 1 Then
      If cell.Column <> 2 Then
        cell.Value = Application.Proper(cell.Value)
      End If
    Next
    Application.EnableEvents = True

 

[MARK858]

Re: Proper case code not working correctly

What happens if you change

For Each cell In Target
      If cell.Column <> 1 Then
      If cell.Column <> 2 Then
        cell.Value = Application.Proper(cell.Value)
      End If
    Next

to

     If Target.Column > 2 Then Target.Value = Application.Proper(Target.Value)

Else remove/comment out your error handler, post what your error says and on what line.
Although your error is probably because you are missing an End If

 

[steve400243]

Re: Proper case code not working correctly

That gives me - "compile error End if without block if"

 

[bhandari]

Re: Proper case code not working correctly

if the statement end,Add end if

End if

 

[MARK858]

Re: Proper case code not working correctly

That gives me - "compile error End if without block if"

It can't give you that error it is all one line and as I and the next post stated you are missing an End If in your original code.

 

[steve400243]

Re: Proper case code not working correctly

sorry for not understanding this Mark, but I cant get it to work. So much to learn. This code below, provided by Mr Rothstein, works fine for excluding col B, I need it to also exclude column A?

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 = Application.Proper(cell.Value)
      End If
      Next
 Application.EnableEvents = True
 End Sub

 

[Joe4]

Re: Proper case code not working correctly

I have merged your two threads together.

Please do NOT start a new thread on the same question. Since it has to do with the original solution provided to you, just continue on in that thread (that way, those who came up with the solution can help you adjust/tweak/debug it).

 

[steve400243]

Re: Proper case code not working correctly

I have merged your two threads together.

Please do NOT start a new thread on the same question. Since it has to do with the original solution provided to you, just continue on in that thread (that way, those who came up with the solution can help you adjust/tweak/debug it).

Thanks Joe4, wasn't sure about that, so just started the new one. Appreciate the tip.

 

[MARK858]

Re: Proper case code not working correctly

sorry for not understanding this Mark, but I cant get it to work. So much to learn. This code below, provided by Mr Rothstein, works fine for excluding col B, I need it to also exclude column A?

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 [COLOR="#FF0000"][B]>[/B][/COLOR] 2 Then
        cell.Value = Application.Proper(cell.Value)
      End If
      Next
 Application.EnableEvents = True
 End Sub