By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 1,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

jumping to another textbox...

P: 35
HI me again MR. Newbie 2 vba. I have another situation I can't seem to figure out. I have 8 rows of text boxs (3 in each row - Quantity, Parts & Amount). My problem is that I have them tabbed accordingly 1 thru 24. But what if there is only one row used then I have to tab thru all the other text boxes to get to the next focus or controling text box (total materials). Is there a way I can code this so that once Im done with whatever row (whether it be row 1 or row 7) that it just goes to the total materials textbox.

Thank you
Jul 24 '10 #1

✓ answered by Guido Geurs

Q2 - the error in your test file is that there is no "End Sub" between the sub's
"Private Sub cmdenter_click()"
and
"Private Sub cmdclr_click()"

I'm sorry but I had send you the code for VB6
This is the code for VBA Excel= (see also attachment)

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub cmdenter_click()
  4.    If text1 = "" Then
  5.       text1.SetFocus
  6.       Exit Sub
  7.    End If
  8.    If text2 = "" Then
  9.       text2.SetFocus
  10.       Exit Sub
  11.    End If
  12.    If text3 = "" Then
  13.       text3.SetFocus
  14.       Exit Sub
  15.    End If
  16.    If text4 = "" Then
  17.       text4.SetFocus
  18.       Exit Sub
  19.    End If
  20. End Sub
  21.  
  22. Private Sub cmdclr_click()
  23.    text1.Value = ""
  24.    text2.Value = ""
  25.    text3.Value = ""
  26.    text4.Value = ""
  27.    Text5.Value = ""
  28. End Sub
  29.  
  30. Private Sub cmdexit_click()
  31.    Unload Me
  32. End Sub
  33.  
  34. Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  35.    If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
  36. End Sub
  37.  
  38. Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  39.    If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
  40. End Sub
  41.  
  42. Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  43.    If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
  44. End Sub
  45.  
  46. Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  47.    If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
  48. End Sub
How it works:
With the TAB key you jumps from textbox to NEXT textbox.
With CTRL+TAB you jumps direct to Textbox5 (= Texttotal).
Shift = 2 is the code for CTRL
KeyCode = 9 is the code for TAB

Share this Question
Share on Google+
10 Replies


Guido Geurs
Expert 100+
P: 767
Is it possible to give the first 24 textboxes the same name but with an index ?
If so, you can use next code in which a CTRL+TAB jumps to the "total" textbox. (see attachment)

Expand|Select|Wrap|Line Numbers
  1. Private Sub Text1_KeyDown(Index As Integer, KeyCode As Integer, Shift As Integer)
  2.    If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
  3. End Sub
If you can't index the textboxes 1 to 24, just use the code for each textbox like:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
  2.    If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
  3. End Sub
  4. Private Sub Text2_KeyDown(KeyCode As Integer, Shift As Integer)
  5.    If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
  6. End Sub
  7. Private Sub Text3_KeyDown(KeyCode As Integer, Shift As Integer)
  8.    If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
  9. End Sub
  10. ...
  11. ...
  12. Private Sub Text24_KeyDown(KeyCode As Integer, Shift As Integer)
  13.    If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
  14. End Sub
Attached Files
File Type: zip jumping to another textbox_v1.zip (1.6 KB, 80 views)
Jul 24 '10 #2

P: 35
@ggeu
Thank you for your response, But I'm not quite sure what you mean. Does this mean that when the user is inputting data into the text boxs she has to use the shift key to skip to the text total box?
Jul 24 '10 #3

P: 35
@ggeu
I attempted to test your code in another user form and this is what I got. "procedure declaration does not match description of event or procedure having the same name. did I do something wrong?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdenter_click()
  2. If Text1 = "" Then
  3. Text1.SetFocus
  4. Exit Sub
  5. End If
  6.  
  7. If Text2 = "" Then
  8. Text2.SetFocus
  9. Exit Sub
  10. End If
  11.  
  12. If Text3 = "" Then
  13. Text3.SetFocus
  14. Exit Sub
  15. End If
  16.  
  17. If Text4 = "" Then
  18. Text4.SetFocus
  19. Exit Sub
  20. End If
  21.  
  22. End Sub
  23. Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
  24.    If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
  25. End Sub
  26. Private Sub Text2_KeyDown(KeyCode As Integer, Shift As Integer)
  27.    If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
  28. End Sub
  29. Private Sub Text3_KeyDown(KeyCode As Integer, Shift As Integer)
  30.    If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
  31. End Sub
  32.  
  33. Private Sub Text4_KeyDown(KeyCode As Integer, Shift As Integer)
  34.    If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
  35. End Sub
  36.  
  37. Private Sub cmdclear_click()
  38. Text1 = ""
  39. Text2 = ""
  40. Text3 = ""
  41. Text4 = ""
  42.  
  43. End Sub
  44.  
  45.  
  46. Private Sub cmdexit_click()
  47. Unload Me
  48.  
  49. End Sub
  50.  
Jul 24 '10 #4

Guido Geurs
Expert 100+
P: 767
@vbanewbie2
Q1: when the user is typing in the textboxes 1 to 24 and press the TAB then the cursor will jump to the next textbox of 24.
When he press the CTRL and the TAB then the cursor will jump to TextboxTotal like you asked for.
The SHift in the code is the name of the variable with the number of the key who is pressed at the same time with the TAB key: it can be CTRL or ALT or SHIFT...

Q2- for the error: is it possible to attach your userform in BYTES so I can test it and see what goes wrong ?
Jul 24 '10 #5

P: 35
@ggeu
take a look and let me know, I only used 5 text boxes in this example
Attached Files
File Type: zip testing.zip (11.9 KB, 76 views)
Jul 24 '10 #6

Guido Geurs
Expert 100+
P: 767
Q2 - the error in your test file is that there is no "End Sub" between the sub's
"Private Sub cmdenter_click()"
and
"Private Sub cmdclr_click()"

I'm sorry but I had send you the code for VB6
This is the code for VBA Excel= (see also attachment)

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub cmdenter_click()
  4.    If text1 = "" Then
  5.       text1.SetFocus
  6.       Exit Sub
  7.    End If
  8.    If text2 = "" Then
  9.       text2.SetFocus
  10.       Exit Sub
  11.    End If
  12.    If text3 = "" Then
  13.       text3.SetFocus
  14.       Exit Sub
  15.    End If
  16.    If text4 = "" Then
  17.       text4.SetFocus
  18.       Exit Sub
  19.    End If
  20. End Sub
  21.  
  22. Private Sub cmdclr_click()
  23.    text1.Value = ""
  24.    text2.Value = ""
  25.    text3.Value = ""
  26.    text4.Value = ""
  27.    Text5.Value = ""
  28. End Sub
  29.  
  30. Private Sub cmdexit_click()
  31.    Unload Me
  32. End Sub
  33.  
  34. Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  35.    If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
  36. End Sub
  37.  
  38. Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  39.    If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
  40. End Sub
  41.  
  42. Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  43.    If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
  44. End Sub
  45.  
  46. Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  47.    If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
  48. End Sub
How it works:
With the TAB key you jumps from textbox to NEXT textbox.
With CTRL+TAB you jumps direct to Textbox5 (= Texttotal).
Shift = 2 is the code for CTRL
KeyCode = 9 is the code for TAB
Jul 25 '10 #7

P: 35
that code you gave me works great it skips over the other textboxs like i needed it to. only problem is that when I get done with the other 6 check boxes that follow it it, txttotalmaterial, txtmisc, txtshop, txthome, txttax, txttotal then hit enter it goes back to the next box that is available (out of the ones we skipped). another words i get to the end of the form (txttotal) put in my amount hit enter an it goes to txtqnty2, i have set the focus so it goes back to the beginning but is still does this, can u help?
Attached Files
File Type: zip AAA APPLIANCE.zip (44.0 KB, 77 views)
Jul 26 '10 #8

Guido Geurs
Expert 100+
P: 767
Q1 - I can't find txttotalmaterial, txtmisc, txtshop, txthome, txttax, ????

Q2 - (see attachment) I have tested with TAB and CTRL+TAB jump from txtamt2 to txttotmat and it works.
The code is for CTRL and TAB , not for ENTER!
ENTER follows the normal rules= next TABSTOP !
When I am at Txttotal and hit ENTER (or TAB), it jumps to the CommandButton1 = "Enter" !
Attached Files
File Type: zip jumping to another textbox_AVI.zip (78.0 KB, 61 views)
Jul 27 '10 #9

Guido Geurs
Expert 100+
P: 767
You can check each element in your form with this code (It will reduce the length of your code !!)

Expand|Select|Wrap|Line Numbers
  1. Dim CTRL As Control
  2.    For Each CTRL In Me.Controls
  3.       If Left(CTRL.Name, 3) = "txt" Then
  4.          If Trim(CTRL.Value) = "" Then
  5.             MsgBox "Please enter the right input!"
  6.             CTRL.SetFocus
  7.             Exit Sub
  8.          End If
  9.       End If
  10.       If Left(CTRL.Name, 5) = "Check" Then
  11.          If CTRL.Value = False Then
  12.             MsgBox "Please Check!"
  13.             CTRL.SetFocus
  14.             Exit Sub
  15.          End If
  16.       End If
  17.    Next
Jul 27 '10 #10

Guido Geurs
Expert 100+
P: 767
If you want dedicated Msgbox, You can use "Select Case..." like:

Expand|Select|Wrap|Line Numbers
  1. Dim CTRL As Control
  2. Dim MESSAGE As String
  3.    For Each CTRL In Me.Controls
  4.       With CTRL
  5.          If Left(.Name, 3) = "txt" Then
  6.             If .Value = "" Then
  7.                Select Case .Name
  8.                   Case "txtname1": MESSAGE = "Please enter the Client's first name!"
  9.                   Case "txtname2": MESSAGE = "Please enter the Client's last name!"
  10.                   Case "txtscheddate": MESSAGE = "Please enter the Date job was completed!"
  11.                   Case Else: MESSAGE = "Enter a valid input!"
  12.                End Select
  13.                MsgBox MESSAGE
  14.                .SetFocus
  15.                Exit Sub
  16.             End If
  17.          End If
  18.          If Left(.Name, 5) = "Check" Then
  19.             If .Value = False Then
  20.                MsgBox "Please Check!"
  21.                .SetFocus
  22.                Exit Sub
  23.             End If
  24.          End If
  25.       End With
  26.    Next
PS: Trim() is not necessary because an entry with only Space is also= ""
Jul 27 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.