How to maintain cursor position when enforcing Change() on a UserForm TextBox?

Question

Suppose I have this following code in my EXCEL VBA UserForm:

Private Sub TextBox_Change()

TextBox.Value = UCase(TextBox.Value)

OtherText.Value = "FOO " & TextBox.value & " BAR"

End Sub

It forces the textbox to be always upper case, and works well when users type from the end.

However say I was given a text "HELLO WORLD" and I wanted to insert "CRUEL " in between (typed manually by users), as soon as I type in C, the cursor would jump to after D and the end result would become "HELLO CWORLDRUEL " (if I typed blindly).

The cursor position wouldn't change if I had caps lock on, but it defeats the purpose of the code... and with a lower case character it always kicks the cursor to the end.

Is there a way to maintain the cursor position for these type of forced-case changes?

I had considered using _Exit() method but I do want to keep both the OtherText and TextBox value uppercase with each change. Otherwise OtherText would be all uppercase, while TextBox is still lowercase before the _Exit() is executed and it looks... undesirable.

Answer 1

Try using a different event, such as the following:

Private Sub TextBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
   If KeyAscii >= 97 And KeyAscii <= 122 Then 'a-z
      KeyAscii = Asc(UCase(Chr(KeyAscii)))
   End If
End Sub

Answer 2

You can capture the cursor position using SelStart, then reset the cursor when you're done manipulating the text.

Option Explicit

Private Sub TextBox1_Change()
    Dim cursorPos As Long
    cursorPos = Me.TextBox1.SelStart
    Me.TextBox1.Text = UCase(Me.TextBox1.Text)
    Me.TextBox1.SelStart = cursorPos
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = True
    Me.Hide
End Sub