Excel VBA VBA Case Function

Question

I found the old entry about the offer suggestion function at the following link. Convert to phrase sentences with vba

I like the following function developed by bretdj

Function ProperCaps(strIn As String) As String
Dim objRegex As Object
Dim objRegMC As Object
Dim objRegM As Object
Set objRegex = CreateObject("vbscript.regexp")
strIn = LCase$(strIn)
With objRegex
    .Global = True
    .ignoreCase = True
     .Pattern = "(^|[\.\?\!\r\t]\s?)([a-z])"
    If .test(strIn) Then
        Set objRegMC = .Execute(strIn)
        For Each objRegM In objRegMC
            Mid$(strIn, objRegM.firstindex + 1, objRegM.Length) = UCase$(objRegM)
        Next
    End If
    MsgBox strIn
End With

What I cannot understand is how to get the function to prescribe the case of a string entered in a specific cell, and then put the corrected sentence back into the original cell. I do not need it in the message box. Something similar to the following:

If Not Intersect(Target, myrange2) Is Nothing Then
    Target.Value = ProperCaps(Target.Value)
End If

Any help would be greatly appreciated. Forgive me for rewriting this; I am not authorized to comment on posts.

Thanks gary

Answer 1

, - End Function, MsgBox strIn ProperCaps = strIn:

---

Option Explicit

Function ProperCaps(strIn As String) As String

    Dim objRegex As Object
    Dim objRegMC As Object
    Dim objRegM As Object

    Set objRegex = CreateObject("vbscript.regexp")
    strIn = LCase$(strIn)

    With objRegex
        .Global = True
        .ignoreCase = True
        .Pattern = "(^|[\.\?\!\r\t]\s?)([a-z])"

        If .test(strIn) Then
            Set objRegMC = .Execute(strIn)

            For Each objRegM In objRegMC
                Mid$(strIn, objRegM.firstindex + 1, objRegM.Length) = UCase$(objRegM)
            Next
        End If
    End With

    ProperCaps = strIn

End Function