You can use the Exit event to test whether the user should be allowed
to exit the text box and move to the next control. For example,

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.TextBox1
If .Text < "abc" Then
.SelStart = 0
.SelLength = Len(.Text)
Cancel = True
End If
End With
End Sub

Here, if the text is not "abc", the text box remains in focus.

Sometimes it may be useful to have an override of this validation so
that the user (or, often, just the developer) can exit the text box
even if the validation fails. I usually provide this by testing the
state of the SHIFT key. If it is down, the validation is skipped.

Go to http://www.cpearson.com/excel/KeyTest.aspx and download the zip
file. Unzip it to some folder and then Import the modKeyState module
into your project. Then test the state of the SHIFT key:


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsShiftKeyDown() = True Then
Exit Sub
End If
With Me.TextBox1
If .Text < "abc" Then
.SelStart = 0
.SelLength = Len(.Text)
Cancel = True
End If
End With
End Sub

Here, if the SHIFT key is not pressed, focus remains in the text box
unless the content is "abc". If the SHIFT key is down, focus moves to
the next control.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Thu, 29 Jan 2009 04:46:01 -0800, dhstein
wrote:

I have a TextBox Control in Access and I'm trying to do something similar in
Excel. In Access I can use the event txbMyTextBox_AfterUpdate() and then
process the full string after the user hits "Enter". But in Excel I just get
the event txbMyTextBox_Change(). So it would seem that I have to process
each character, store the results in a string array and check for the "Enter"
key.

1) Is this correct - or am I missing something?

2) What is the syntax of a string array so that I can read in the data?

3) What is the string compare function that I can use to compare 2 strings?

Thanks for any help you can provide