VBA - How to autocomplete a word in the middle of Textbox

Question

Hello! I am developing a VBA project whose main input is a function f(x,y). I still find it difficult to configure the Textbox to limit the entries, for example: when inserting the "c" character, I would like it to appear "cos" to avoid input errors. However, I am not able to think of a solution that can 'autocomplete' anywhere in the text.

Another problem that also could not think of a solution would be if the user decided to delete some character of that single word, e.g. "Cs". In that case, I would like the whole word "cos" to be erased, or rather "cos" to be one.

Private Sub ctx_funcao_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim MyStr As String
Dim pos As Integer

Select Case KeyAscii
        Case 8 '//Backspace (seta de apagar)
        Case 99 '//c
        Case 108 '//l
        Case 115 '//s
        Case 116 '//t
        Case 40 To 57 '//Números de 0 a 9 e operadores matemáticos
        Case 94 ' ^
        Case 120 To 121 ' x e y
         
        Case Else
            KeyAscii = 0 '//Não deixa nenhuma outra caractere ser escrito
    End Select

End Sub

So far I have only this of settings. I appreciate any help from now!

Answer 1

Hi, h2ocampioni!

Input validation is always a problem, I do not like to lock or check after each character because it is necessary to think of all possible situations.

It is more elegant to check on lostfocus (when you press tab or leave the field), something very common in CPF and CNPJ typing where you have a DV to answer whether the number is right or wrong.

I suggested solving two problems you reported, identifying corrupted patterns, and autocompleting as soon as a letter appears that you expect.

Since of course you have a defined pattern that is not fully exposed in the question, you can set off with the adjustments from there

 Dim MyStr As String
    Dim pos As Integer
    
    Select Case KeyAscii
            Case 8 '//Backspace (seta de apagar)
            Case 99 '//c
               KeyAscii = 0
               TextBox1.Text = TextBox1.Text & "cos"
             
            Case 108 '//l
            Case 115 '//s
            Case 116 '//t
            Case 40 To 57 '//Números de 0 a 9 e operadores matemáticos
            Case 94 ' ^
            Case 120 To 121 ' x e y
             
            Case Else
                KeyAscii = 0 '//Não deixa nenhuma outra caractere ser escrito
        End Select
    
      If InStr(1, TextBox1.Text, "cs", vbTextCompare) > 0 Then
         TextBox1.Text = Replace(TextBox1.Text, "cs", "cos")
      End If