Userform text box question

[paulsolar]

Hi All

I have a question, I'm not sure if it is possible. If it is I'll have a go at doing it.

Is it possible to capitalize the first letter of of each row in a Userform text box?

I have a text box where the user can enter multiple lines and my pet hate is when the users do not keep to the correct capitalization. Proper case wouldn't work as every word would be capitalized.

Is this possible

kind regards

Paul

 

[Domenic]

The following code uses the BeforeUpdate event handler. Change the name of the textbox accordingly.

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

    Dim txt As String
    txt = Me.TextBox1.Value
    
    Dim arr() As String
    arr() = Split(txt, vbCrLf)
    
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Dim tmp As String
        tmp = arr(i)
        If Len(tmp) > 0 Then
            Mid(tmp, 1, 1) = UCase(Mid(tmp, 1, 1))
            arr(i) = tmp
        End If
    Next i
    
    Me.TextBox1.Value = VBA.Join(arr, vbCrLf)
    
End Sub

Hope this helps!

 

[paulsolar]

Hi Domenic

worked like a charm, brilliant thanks. I managed to get the very first letter to go to a capital and tried everything and couldnt improve on it.

Look out Fluff, you have a hard act to follow after this (only joking, hope you are keeping well)

thanks again

cheers

Paul

 

[GWteB]

You might also be interested in the code below ...

Public Function ProperCaseOnSentence(ByRef argText As String) As String

    Const DELIMS As String = ".?!" & vbLf

    If Len(argText) = 1 Then
        ProperCaseOnSentence = UCase(argText)
    ElseIf Len(argText) > 1 Then
        ProperCaseOnSentence = CapitalizeAfter(argText, DELIMS)
    End If
End Function

Private Function CapitalizeAfter(ByVal argText As String, ByVal argDelims As String) As String
    Dim arr As Variant, delim As String, i As Long, n As Long
    CapitalizeAfter = argText
    For i = 1 To Len(argDelims)
        delim = VBA.Mid$(argDelims, i, 1)
        If InStr(CapitalizeAfter, delim) > 0 Then
            arr = Split(CapitalizeAfter, delim)
            For n = LBound(arr) To UBound(arr)
                arr(n) = Capitalize(arr(n))
            Next n
            CapitalizeAfter = Join(arr, delim)
        Else
            CapitalizeAfter = Capitalize(CapitalizeAfter)
        End If
    Next i
End Function

Private Function Capitalize(ByVal argText As String) As String
    Dim i As Long, chr As String
    If Len(argText) > 0 Then
        Capitalize = argText
        For i = 1 To Len(Capitalize)
            chr = VBA.Mid$(Capitalize, i, 1)
            If chr Like "[A-ž]" Then
                Mid$(Capitalize, i, 1) = UCase(chr)
                Exit For
            End If
        Next i
    End If
End Function

Usage example:

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Me.TextBox1.Value = ProperCaseOnSentence(Me.TextBox1.Value)
End Sub