How do I get Tab to move to next field?

[laxcat73]

This is my code: I know it's not pretty but it works. My question is how can I press tab and go to the next selection? Right now, it goes from:
>Employee Paid
>>Candidate Referred
>>>Date of Hire
>>>>Department
>>>>>Employee Paid File Number
>>>>>>Employee Paid Location
>>>>>>>Experience
>>>>>>>>Dual ref
>>>>>>>>>Top Collector
>>>>>>>>>>Buttons

Ima

Private Sub cmdCreate_Click()
Dim lRow As Long
Dim lPaid As Long
Dim ws As Worksheet
Set ws = Worksheets("Master")
'find  first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
 
lPaid = Me.txtPaid.AutoSize
'check for entries
If Trim(Me.txtPaid.Value) = "" Then
  Me.txtPaid.SetFocus
  MsgBox "Please enter a referrer"
  Exit Sub
End If
If Trim(Me.cboLoc.Value) = "" Then
  Me.cboLoc.SetFocus
  MsgBox "Please enter a location for employee paid"
  Exit Sub
End If
If Trim(Me.txtRef.Value) = "" Then
  Me.txtRef.SetFocus
  MsgBox "Please enter a referral candidate"
  Exit Sub
End If
If Trim(Me.txtDate.Value) = "" Then
  Me.txtDate.SetFocus
  MsgBox "Please enter a Date of Hire"
  Exit Sub
End If
If Trim(Me.cboExp.Value) = "" Then
  Me.cboExp.SetFocus
  MsgBox "Please enter a payment program"
  Exit Sub
End If
If Trim(Me.cboRef.Value) = "" Then
  Me.cboRef.SetFocus
  MsgBox "Please enter if there were dual referrers"
  Exit Sub
End If
 
If Trim(Me.cboTC.Value) = "" Then
  Me.cboTC.SetFocus
  MsgBox "Please enter if referrer is a top collector"
  Exit Sub
End If
 
'copy the data to the database
With ws
  .Cells(lRow, 3).Value = Me.txtPaid.Value
  .Cells(lRow, 2).Value = Me.txtFile.Value
  .Cells(lRow, 4).Value = Me.txtRef.Value
  .Cells(lRow, 8).Value = Me.txtDate.Value
  .Cells(lRow, 5).Value = Me.txtDept.Value
  .Cells(lRow, 11).Value = Me.cboExp.Value
  .Cells(lRow, 9).Value = Me.cboRef.Value
  .Cells(lRow, 10).Value = Me.cboTC.Value
      Select Case Me.cboLoc.Value
            Case "Fairport"
            .Cells(lRow, 1).Value = "Y93"
            Case "Buffalo"
            .Cells(lRow, 1).Value = "6VP"
      End Select
End With
'clear the data
Me.txtPaid.Value = ""
Me.cboLoc.Value = ""
Me.txtFile.Value = ""
Me.txtRef.Value = ""
Me.txtDept.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.cboExp.Value = ""
Me.cboRef.Value = ""
Me.cboTC.Value = ""
Me.txtPaid.SetFocus
End Sub
 
Private Sub cmdCancel_Click()
  Unload Me
End Sub
 
Private Sub UserForm_Initialize()
Dim cExp As Range
Dim cRef As Range
Dim cTC As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("Master")
For Each cLoc In ws.Range("Location")
  With Me.cboLoc
    .AddItem cLoc.Value
  End With
Next cLoc
For Each cExp In ws.Range("Program")
  With Me.cboExp
    .AddItem cExp.Value
  End With
Next cExp
For Each cRef In ws.Range("Response")
  With Me.cboRef
    .AddItem cRef.Value
  End With
Next cRef
For Each cTC In ws.Range("Response")
  With Me.cboTC
    .AddItem cTC.Value
  End With
Next cTC
 
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtPaid.Value = ""
Me.txtRef.Value = ""
Me.txtFile.Value = ""
Me.cboLoc.Value = ""
Me.cboTC.Value = ""
Me.cboRef.Value = ""
Me.cboExp.Value = ""
Me.txtDept.Value = ""
Me.txtPaid.SetFocus
End Sub
 
Private Sub cmdClear_Click()
    Call UserForm_Initialize
End Sub

Is there something in here that gives TAB the command of moving specifically to a line? Is there a way to code this?

 

[laxcat73]

Forgot to add a picture of userform
http://imageshack.us/photo/my-images/846/unledbgx.png/

Edited in above

 

[BeanCounters]

Hi Laxcat,

Being VERY new to VBA I was also looking for a solution. I found one that works for me, but I wouldn't know how to tell you to insert it into your code. I posted it below if you want to test it out.

'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub cboYOURCBONAME_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter.
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub

Hope it helps.

Julie

 

[laxcat73]

Julie,

thank you for the help. I was wondering if your code was used for a userform? It looks like that code is exactly what Excel does naturally in a spreadsheet - hit tab move to the cell to the right and enter moves to the next row.

Hi Laxcat,

Being VERY new to VBA I was also looking for a solution. I found one that works for me, but I wouldn't know how to tell you to insert it into your code. I posted it below if you want to test it out.

'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub cboYOURCBONAME_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter.
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub

Hope it helps.

Julie

 

[BeanCounters]

Hi Laxcat,

No, my cbobox is not on a form, but directly on a sheet. Like I said I am VERY new to VBA and I don't know if this would work. Try putting the code after:

Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("Master")

But please make a copy of your workbook first. I would hate to mess up something you have that already works for you.

Hope that helps.

Julie