Detecting Ctrl-Tabs in User Form input fields?

I have a user that somehow after entering a date in a user form text box
hit Ctrl-Tab a few times. This put some sort of non-prinatble characters
after the input. Then closing the form put the input in a spreadsheet cell
and formulas dependent on this cell errored out. And then macros reading
these errors crashed. Anyway to prevent or detect input like this?

Don <donwiss at panix.com>.

You can use the Clean function to remove non-printable characters something
like this:

Sheet1.Range("C3").Value = Application.Clean(Sheet1.Range("C2").Value)

This takes the Cell in C2 and removes the non printable characters and
places the resutl in C3

To test it put =CHAR(7) & "Text" in Cell C2...

HTH

"Don Wiss" wrote:

> I have a user that somehow after entering a date in a user form text box
> hit Ctrl-Tab a few times. This put some sort of non-prinatble characters
> after the input. Then closing the form put the input in a spreadsheet cell
> and formulas dependent on this cell errored out. And then macros reading
> these errors crashed. Anyway to prevent or detect input like this?
>
> Don <donwiss at panix.com>.
>

Hello Don,

Add this code in your TextBox's AfterUpdate Event. It will remove any non-printable character from the text. Since this clean up happens after the text is entered, normal textbox operation is unaffected. You need to substitute the name of your textbox's name into the code and also copy the code into that textbox's AfterUpdate Event procedure.
_________________________________________________________________

'Remove Non-Printable Characters Code

Private Sub TextBox1_AfterUpdate()

Dim I As Long
Dim L As Long
Dim AscTxt As Integer
Dim NewTxt As String

L = Len(TextBox1.Text)
For I = 1 To L
AscTxt = Asc(Mid(TextBox1.Text, I, 1))
If AscTxt > 31 Then
NewTxt = NewTxt & Chr(AscTxt)
End If
Next I

TextBox1.Text = NewTxt

End Sub
_________________________________________________________________

Hope this helps,
Leith Ross