Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change event; return focus to original textbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
change event; return focus to original textbox
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. -- Regards, Tom Ogilvy "matt" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
change event; return focus to original textbox
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" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
change event; return focus to original textbox
On Feb 22, 1:28 pm, "Chip Pearson" wrote:
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) "matt" wrote in message oups.com... 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- Hide quoted text - - Show quoted text - Thank you Tom and Chip for the assistance. It is very helpful. Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox.change event not firing! | Excel Programming | |||
Is there a VBA Event to Detect Change in ActiveCell Focus? | Excel Programming | |||
help with textbox change event | Excel Programming | |||
Textbox change event | Excel Programming | |||
event which occurs when a TextBox receives focus | Excel Programming |