Populate UserForm and then move to next record without searching

[passman86]

Hi everybody.

I have returned for more expert excel knowlegde.

I have created a userform, a screenshot of which I have created. Everything in it works very nicely as long as you are searching for a record with the Unique 3 digit ID number typed into the ID textbox.

This is fine when updating individual records when you have plenty of time, but this is now going to be used to update records on the fly directly in the meeting. This means I need to be able to tab through all the records, one by one.

form.jpg

So, above is the form I have created.

Private Sub CmdFind_Click()

         If txtID.Value = "" Then
             MsgBox ("Please Enter Valid ID")
             txtID = ""
             txtID.SetFocus
             Exit Sub
             Else
Rw = Worksheets("Tasks").Range("B:B").Find(Me.txtID.Value, LookIn:=xlValues, LookAt:=xlPart).Row


txtID.Value = Range("B" & Rw).Value
         txtTask.Value = Range("C" & Rw).Value
         cmbSys.Value = Range("D" & Rw).Value
         CmbName.Value = Range("E" & Rw).Value
         txtDateAdd.Value = Format(Range("F" & Rw).Value, "dd/mm/yyyy")
         txtLead.Value = Range("G" & Rw).Value
         txtForecast.Value = Format(Range("J" & Rw).Value, "dd/mm/yyyy")
         CmbStatus.Value = Range("L" & Rw).Value
         txtCloseDate.Value = Format(Range("M" & Rw).Value, "dd/mm/yyyy")
         txtComment.Value = Range("N" & Rw).Value
         
             End If
          
End Sub

Above is the code I have that searches for the record related to the ID number.

Is it possible to get the userform to display the first record in the table, and then move through to the next record using the next button.

What I must say as well is that the ID number column is not sorted numerically, the table is sorted numerically by another column, the System column shown in the screen below, that does not use unique numbers.

spreadsheet.jpg

Above is the layout of the spreadsheet that I have created and how its all displayed.

Is there code to do this? If I have to search for the first record that is fine, I would just like to know the code to allow me to step through the records on the spreadsheet.

[VBA FTW]

I think on the workbook_open event you'd want to select the cell you want to begin on (you may want to do this at a different time depending on your process). Then put

ActiveCell.Offset(0, 1).Select

at the beginning of the code for the Next button. this code should move the active cell to the right by 1 cell when you click the button. You'd then want to set your textbox.value equal to ActiveCell.value in the Next button macro. If you want to move down instead of right, switch the 0,1 to 1,0

[VBA FTW]

I'm sorry, I meant put the ActiveCell.Offset at the end of your code