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]

Re: Proper case code not working correctly

Wow, that was easy, the one thing I didn't try.... Thanks a lot for your help Mark, have a nice weekend.

If cell.Column > 2 Then

 

[MARK858]

Re: Proper case code not working correctly

You're welcome (a bit surprised Rick didn't find a way to do it with one of his evaluate codes though ).

 

[steve400243]

Re: Proper case code not working correctly

At the time I only asked for it to exclude Column B, which it did. It was after that that I needed it for Column A also.

 

[steve400243]

Re: Proper case code not working correctly

I did not write the original code nor did I examine exactly what it was doing, I simply added the missing statement that caused the error. But you are right, there is an Evaluate solution available...

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  Target.Value = Evaluate("PROPER(" & Target.Address & ")")
  Application.EnableEvents = True
End Sub

Thanks again Rick, I will try this this one also. Your solution yesterday worked fine. Have a nice weekend.

 

[Rick Rothstein]

Re: Proper case code not working correctly

You're welcome (a bit surprised Rick didn't find a way to do it with one of his evaluate codes though ).

I did not write the original code nor did I examine exactly what it was doing, I simply added the missing statement that caused the error. But you are right, there is an Evaluate solution... see below.

Thanks again Rick, I will try this this one also. Your solution yesterday worked fine. Have a nice weekend.

You were too quick for me. I actually deleted that one as it did not restrict the action to Column C and greater plus it had a couple of potential problems. This is the code you should use...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  On Error Resume Next
  Target.Value = Evaluate(Replace("IF(@="""","""",IF(COLUMN(@)>2,PROPER(@),@))", "@", Target.Address))
  On Error GoTo 0
  Application.EnableEvents = True
End Sub[/td]
[/tr]
[/table]

Note: The two On Error lines of code are there to protect against you deleting all the cells on the sheet at once... if you will never be doing that, you can remove them.

 

[steve400243]

Re: Proper case code not working correctly

Thanks again, could you please explain this (Replace("IF(@="""","""",IF(COLUMN(@)>2,PROPER(@),@))", "@"

 

[Rick Rothstein]

Re: Proper case code not working correctly

Thanks again, could you please explain this (Replace("IF(@="""","""",IF(COLUMN(@)>2,PROPER(@),@))", "@"

The argument to the Evaluate function is a text string... the @ symbol is simply a place holder for a longer piece of text and the VBA Replace function substitutes the address for the Target range for those @ symbols. Doing it this way makes the Excel formula that I am constructing for the Evaluate function easier to read while I am developing it, otherwise I every place you see an @ symbol, I would have had to manually type the following into the text at their locations...

" & Target.Address & "

 

[steve400243]

Re: Proper case code not working correctly

The argument to the Evaluate function is a text string... the @ symbol is simply a place holder for a longer piece of text and the VBA Replace function substitutes the address for the Target range for those @ symbols. Doing it this way makes the Excel formula that I am constructing for the Evaluate function easier to read while I am developing it, otherwise I every place you see an @ symbol, I would have had to manually type the following into the text at their locations...

" & Target.Address & "

Ah ok, thank you for explaining. I have never seen that done before. Have a nice weekend.

 

[Rick Rothstein]

Re: Proper case code not working correctly

Ah ok, thank you for explaining. I have never seen that done before. Have a nice weekend.

I invented it... well, more probably rediscovered it as it would be hard to believe in all the years VB has been included with Excel that I am the only one too lazy to want to type all the extra text when a simpler method existed.:wink: