change event; return focus to original textbox

[matt]

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

 

[Tom Ogilvy]

In the exit event of the textbox, validate the entry. If it is invalid, set
the cancel property to true and the user won't exit the textbox.

 

[Chip Pearson]

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]

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, LLCwww.cpearson.com
(email address is on the web site)












- Show quoted text -

Thank you Tom and Chip for the assistance. It is very helpful.

Matt