Microsoft Office Forums - View Single Post - [Solved] VBA to convert a line of text to title case

jeffreybrown · 09-04-2019, 08:38 AM

I found this link which seems in the right direction; however, looking to start the proper case change after an em dash. I know the change is required in the Blue sections of the code below, but not understanding how to change it. The part about looking for the period I don't even need as there are no periods in the sentence.

Before
AFMAN—AIR FORCE MANUAL
AO—ACTION OFFICER
CAF—CENTRAL ADJUDICATION FACILITY

AFTER
AFMAN—Air Force Manual
AO—Action Officer
CAF—Central Adjudication Facility

Code:

Sub MakeTitle()
    Application.ScreenUpdating = False
    Dim StrTmp As String
    With Selection.Range
      StrTmp = Trim(.Text)
      While Right(StrTmp, 1) = "."
        StrTmp = Left(StrTmp, Len(StrTmp) - 1)
      Wend
      While InStr(StrTmp, "  ") > 0
        StrTmp = Replace(StrTmp, "  ", " ")
      Wend
      StrTmp = TitleCase(StrTmp, bCaps:=False, bExcl:=False)
      .Text = StrTmp
    End With
    Application.ScreenUpdating = True
End Sub