Matt,
The easiest way is to use the Exit event procedure, test the value of the
text box, and set Cancel equal to True if it is an invalid entry. For
example
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.TextBox1
If Int(.Text) < 0 Or Int(.Text) > 5 Then
.SelStart = 0
.SelLength = Len(.Text)
Cancel = True
End If
End With
End Sub
The only problem with that is that it gives the users no escape mechanism if
they want to completely cancel out of the form. I usually write a "back
door" to allow the user to exit with an invalid value, such as holding down
the SHIFT key. E.g,
Private Declare Function GetKeyState Lib "user32" ( _
ByVal nVirtKey As Long) As Integer
''''''''''''''''''''''''''''''''''''''''''
' This constant is used in a bit-wise AND
' operation with the result of GetKeyState
' to determine if the specified key is
' down.
''''''''''''''''''''''''''''''''''''''''''
Private Const KEY_MASK As Integer = &HFF80 ' decimal -128
'''''''''''''''''''''''''''''''''''''''''
' KEY CONSTANTS. Values taken
' from VC++ 6.0 WinUser.h file.
'''''''''''''''''''''''''''''''''''''''''
Private Const VK_LSHIFT = &HA0
Private Const VK_RSHIFT = &HA1
Private Const VK_LCONTROL = &HA2
Private Const VK_RCONTROL = &HA3
Private Const VK_LMENU = &HA4 ' LEFT ALT KEY
Private Const VK_RMENU = &HA5 ' RIGHT ALT KEY
'''''''''''''''''''''''''''''''''''''''''
Private Function IsShiftKeyDown() As Boolean
Dim Res As Long
Res = GetKeyState(vbKeyShift) And KEY_MASK
IsShiftKeyDown = CBool(Res)
End Function
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.TextBox1
If Int(.Text) < 0 Or Int(.Text) > 5 Then
.SelStart = 0
.SelLength = Len(.Text)
If IsShiftKeyDown() = False Then
Cancel = True
End If
End If
End With
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"matt" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a user form (i.e. frmGrade) that has a number of text boxes in
> it (i.e. properties window/name = txtExpDesignA, txtExpDesignB,
> txtExpDesignC...). The text boxes are set to 0 (i.e. properties winow/
> value = 0) and the textboxes can take on a value between 0 and 5. I'm
> using an event (for when the user switches text boxes to enter
> additional information) to call a function that validates whether or
> not the text box value is indeed between 0 and 5. If the value is NOT
> between 0 and 5 then I want the program to "reselect" the textbox that
> has the error in it.
>
> For example, assume that the first text box is txtExpDesignA and when
> you hit <tab> the cursor moves to txtExpDesignB, and when you hit
> <tab> again the cursor moves you to txtExpDesignC, and so on.
>
> If I enter 6 in txtExpDesignA (which is outside of the values 0 to 5)
> and hit <tab> the program will tell you that you entered an incorrect
> value for txtExpDesignA. As a result, I want the cursor to stay in
> txtExpDesignA and NOT move to txtExpDesignB on the change. In the
> code below you will notice that I'm using
> frmGrade.txtExpDesignA.SetFocus, but this does not seem to "reselect"
> txtExpDesignA because the program just "tabs" to the next text box
> (i.e. txtExpDesignB); however, if I use
> frmGrade.txtExpDesignC.SetFocus, the program will skip over
> txtExpDesignB and select txtExpDesignC just fine. Can someone clue me
> in on how to "reselect" txtExpDesignA on the event.
>
> An added question would be this: Is there a way to highlight all the
> contents of the text box on using the .SetFocus method rather than
> just moving the cursor? (i.e. if I do txtExpDesignC.SetFocus, the
> cursor blinks at the end of the text...is there a way to have it
> highlight the entire text just like the text is highlighted when you
> hit <tab>?)
>
> Thanks in advance,
>
> Matt
>
> Option Explicit
> Dim score As Boolean
>
> Private Sub txtExpDesignA_AfterUpdate() 'Change
> score = validate5(frmGrade.txtExpDesignA.Value)
> If score = False Then
> MsgBox ("You input an incorrect value in ""A.""" & Chr(13) _
> & "The value must be a number and <= 5.")
> 'frmGrade.txtExpDesignC.SetFocus
> frmGrade.txtExpDesignA.SetFocus
> End If
> End Sub
>
> Private Function validate5(number) As Boolean
> If (IsNumeric(number) = True) And (number <= 5) And (number > 0) Then
> validate5 = True
> Else
> validate5 = False
> End If
> End Function
>
Similar Threads
