Macro for Title Case

[rhmkrmi]

Hello,

Is there a macro that I can use to set the case for a range of cells to Title Case?

Thank you

 

[James006]

Hello,

If you were to be more specific about the structure of your sheet ... a proposal could be drafted ... to produce your expected result ...

 

[Fluff]

Maybe something like

Sub rhmkrmi()
   With Selection
      .Value = Evaluate("if({1},proper(" & .Address & "),"""")")
   End With
End Sub

 

[footoo]

Maybe something like

Sub rhmkrmi()
   With Selection
      .Value = Evaluate("if({1},proper(" & .Address & "),"""")")
   End With
End Sub

Should note that this only works if the selection consists of one area only, and any formulas will be converted to value.

 

[rhmkrmi]

Thank you.

I am trying to make the case of cells A1:A10 tile case, for example, if the user types pRoject OF North West, or PROJECT of north west, the macro changes it to Project of North West.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
      Application.EnableEvents = False
      Target.Value = Application.Proper(Target.Value)
      Application.EnableEvents = True
   End If
End Sub

This needs to go in the relevant sheet module.

 

[rhmkrmi]

Thanks a lot.
It works fine.
How can I make the range dynamic, like, A$1:A10?
Also, is there a way to make words like "of" and "the" always lower case in this macro?

 

[Fluff]

Simple answer no & no.
You can change the range the macro works on, but it cannot be changed dynamically.
To leave certain words lower case, you would need a list of all those words & then have to run comparisons, which is going to make things slow.

 

[footoo]

Thanks a lot.
It works fine.
How can I make the range dynamic, like, A$1:A10?
Also, is there a way to make words like "of" and "the" always lower case in this macro?

I'll assume "dynamic" means whenever data is input to any cell in column A (if it means something else, post again).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ray, i%
If Target.CountLarge > 1 Then Exit Sub
If Target.Column = 1 Then
    ray = Split(Application.Proper(Target), " ")
    For i = 1 To UBound(ray)
        If ray(i) = "Of" Or ray(i) = "The" Then ray(i) = LCase(ray(i))
    Next
    Application.EnableEvents = False
    Target = Join(ray, " ")
    Application.EnableEvents = True
End If
End Sub

 

[rhmkrmi]

Fantastic.

Thank you so very much.