jumping to another textbox...

[vbanewbie2]

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

[Guido Geurs]

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)

Private Sub Text1_KeyDown(Index As Integer, KeyCode As Integer, Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
End Sub

If you can't index the textboxes 1 to 24, just use the code for each textbox like:

Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
End Sub
Private Sub Text2_KeyDown(KeyCode As Integer, Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
End Sub
Private Sub Text3_KeyDown(KeyCode As Integer, Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
End Sub
...
...
Private Sub Text24_KeyDown(KeyCode As Integer, Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
End Sub

[vbanewbie2]

@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?

[vbanewbie2]

@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?

Private Sub cmdenter_click()
If Text1 = "" Then
Text1.SetFocus
Exit Sub
End If
 
If Text2 = "" Then
Text2.SetFocus
Exit Sub
End If
 
If Text3 = "" Then
Text3.SetFocus
Exit Sub
End If
 
If Text4 = "" Then
Text4.SetFocus
Exit Sub
End If
 
End Sub
Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
End Sub
Private Sub Text2_KeyDown(KeyCode As Integer, Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
End Sub
Private Sub Text3_KeyDown(KeyCode As Integer, Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
End Sub
 
Private Sub Text4_KeyDown(KeyCode As Integer, Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextTotal.SetFocus  ' CTRL and TAB
End Sub
 
Private Sub cmdclear_click()
Text1 = ""
Text2 = ""
Text3 = ""
Text4 = ""
 
End Sub
 
 
Private Sub cmdexit_click()
Unload Me
 
End Sub
 

[Guido Geurs]

@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 ?

[vbanewbie2]

@ggeu
take a look and let me know, I only used 5 text boxes in this example

[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)

Option Explicit
 
Private Sub cmdenter_click()
   If text1 = "" Then
      text1.SetFocus
      Exit Sub
   End If
   If text2 = "" Then
      text2.SetFocus
      Exit Sub
   End If
   If text3 = "" Then
      text3.SetFocus
      Exit Sub
   End If
   If text4 = "" Then
      text4.SetFocus
      Exit Sub
   End If
End Sub
 
Private Sub cmdclr_click()
   text1.Value = ""
   text2.Value = ""
   text3.Value = ""
   text4.Value = ""
   Text5.Value = ""
End Sub
 
Private Sub cmdexit_click()
   Unload Me
End Sub
 
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
End Sub
 
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
End Sub
 
Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
End Sub
 
Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   If Shift = 2 And KeyCode = 9 Then TextBox5.SetFocus ' CTRL and TAB
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

[vbanewbie2]

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?

[Guido Geurs]

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" !

[Guido Geurs]

You can check each element in your form with this code (It will reduce the length of your code !!)

Dim CTRL As Control
   For Each CTRL In Me.Controls
      If Left(CTRL.Name, 3) = "txt" Then
         If Trim(CTRL.Value) = "" Then
            MsgBox "Please enter the right input!"
            CTRL.SetFocus
            Exit Sub
         End If
      End If
      If Left(CTRL.Name, 5) = "Check" Then
         If CTRL.Value = False Then
            MsgBox "Please Check!"
            CTRL.SetFocus
            Exit Sub
         End If
      End If
   Next

[Guido Geurs]

If you want dedicated Msgbox, You can use "Select Case..." like:

Dim CTRL As Control
Dim MESSAGE As String
   For Each CTRL In Me.Controls
      With CTRL
         If Left(.Name, 3) = "txt" Then
            If .Value = "" Then
               Select Case .Name
                  Case "txtname1": MESSAGE = "Please enter the Client's first name!"
                  Case "txtname2": MESSAGE = "Please enter the Client's last name!"
                  Case "txtscheddate": MESSAGE = "Please enter the Date job was completed!"
                  Case Else: MESSAGE = "Enter a valid input!"
               End Select
               MsgBox MESSAGE
               .SetFocus
               Exit Sub
            End If
         End If
         If Left(.Name, 5) = "Check" Then
            If .Value = False Then
               MsgBox "Please Check!"
               .SetFocus
               Exit Sub
            End If
         End If
      End With
   Next

PS: Trim() is not necessary because an entry with only Space is also= ""