Specifying Proper Case

Here are two alternate versions of the MakeProper3 macro in my most recent comment below:

Sub MakeProper4()
If Selection.HasFormula = False Then _
    Selection = Application.Proper(Selection)
End Sub

Notice Application.Proper is the same as WorksheetFunction.Proper except the latter produces a debug dialog if Proper returns an error but the former simply returns the error. Both are equivalent to Excel's PROPER function.

Sub MakeProper5()
Dim cell As Range
If Selection.HasFormula = False Then _
    For Each cell In Selection: cell = StrConv(cell, vbProperCase): Next cell
End Sub

Notice PROPER(...) capitalizes the first letter after any non-letter, reducing the rest to lowercase, while StrConv(..., vbProperCase) ignores non-letters except space, Tab, and CR/LF.

Thanks Woolley.

@sandeepkothari
Willy's macro tests an array with a single element (1 or TRUE); I'm not sure why. This version works as well as Willy's:

Sub MakeProper2()
Selection = Evaluate("PROPER(" & Selection.Address & ")")
End Sub

And this version does nothing if Selection includes a formula:

Sub MakeProper3()
If Selection.HasFormula = False Then _
    Selection = Evaluate("PROPER(" & Selection.Address & ")")
End Sub

Notice Selection can be more than one cell.

Thanks to J. Woolley for the link to

https://excelribbon.tips.net/T011267_Modifying_Proper_Capitalization.html

gives the sort of code needed to "Get it Right"

Hi Willy, pl explain why you have used the IF function? I tried without it & did not succeed to get the desired result.

@jamies
See https://excelribbon.tips.net/T011267_Modifying_Proper_Capitalization.html

Problem with "PROPER" is that it does not recognise punctuation appropriately, and such prefixes to "family", or Tribe, "Clan" names or such as "Mac", "Mc", De La etc.

So dealing with "Title" cases needs manual, or scripted handling, or careful use of Substitute actions -
Luckily there is now the Regex facilities.

See such things as REGEXEXTRACT Function

0: Return the first string that matches the pattern.
1: Return all strings that match the pattern as an array.
2: Return capturing groups from the first match as an array.
Note: Capturing groups are parts of a regex pattern surrounded by parentheses "(...)".

If you want to change the case directly in the cell or a range, you can use this one-liner macro:

Sub MakeProper()
Selection = Evaluate("IF({1},PROPER(" & Selection.Address & "))")
End Sub

Of course you can replace PROPER with UPPER or LOWER and the macro will make the changes accordingly.