Some Background.  In the last webpage we developed a userform and event code so that a user could enter data on the form and write it to a spreadsheet.  This is a fairly common application and we developed it in the context of a referee recording his refereeing jobs.  But we want to be able to do more than just write to the spreadsheet, we also want to be able to look at past entries and possibly delete them.

To do this we want to add two command buttons though it would certainly be reasonable to add others

• Move to the next tournament entered in the data source.
• Move to the previous tournament entered in the data source.
• Give the user feedback as to whether they are at the end or beginning of the data source.
• You may want to use a list box or combo box to limit the locations for the tournament to those that are listed in another spreadsheet.  This will provide another level of input validation.

While there are plenty of other things you are likely to want, or related things you might have on a different spreadsheet within the same workbook, we will start with these.  Working these examples should provide you with some more useful working prototypical controls.

Developing the UserForm.  You are going to modify the frmTournament we were using in the last webpage to look like the following.

1. Knoxville in Cell A1.
2. Vanderbilt in Cell A2.
3. Newark in Cell A3.
4. Princeton in Cell A4.
5. Syracuse in Cell A5.
6. Albany in Cell A6.
7. Rochester in Cell A7.

You need to enter the RowSource property so that you can get the entries in the combobox from elsewhere in the workbook.  Obviously it would be possible to create a form to help you with this.  

Remember, if you add more tournament locations to the list in Sheet3, you must modify the RowSource property on the combobox control accordingly.

Private Sub UserForm_Initialize()
' Activating the sheet that contains the data
Sheets("Sheet2").Activate
Cells(1, 1).Select
End Sub

You have already done the work necessary to get the cboLocation combobox to work.  All that is left is the code for the Next Entry and Previous Entry command buttons.

We will start with the cmdPrevious_Click( ) event code.

Private Sub cmdPrevious_Click()
Dim CurrentRow As Integer

' Determine the current row
CurrentRow = Application.ActiveCell.Row
If CurrentRow = 1 Then

MsgBox "You are at the first entry in the data source." & vbCrLf & "You can't_
move to the Previous Entry", vbOKOnly, "Warning!"

Else

ActiveCell.Offset(-1, 0).Select
cboLocation.Text = ActiveCell
txtDate.Text = ActiveCell.Offset(0, 1)
txtDirector.Text = ActiveCell.Offset(0, 2)
txtPay.Text = ActiveCell.Offset(0, 3)

End If
End Sub

This code gives a message when you can't move earlier in the data source.  If you aren't trying to move to entries that don't exist it selects the cell that is one above the current cell and makes it the active cell.  Then it fills in the controls on the form using the offset property relative to this cell.

The code for the cmdNext_Click( ) event is very similar and follows.

Private Sub cmdNext_Click()
Dim CurrentRow As Integer

' Determine the current row
CurrentRow = Application.ActiveCell.Row
If CurrentRow = Application.WorksheetFunction.CountA(Range("A:A")) Then

MsgBox "You are at the last entry in the data source." & vbCrLf & "You cannot _ move to the Next Entry", vbOKOnly, "Warning!"

Else

ActiveCell.Offset(1, 0).Select
cboLocation.Text = ActiveCell
txtDate.Text = ActiveCell.Offset(0, 1)
txtDirector.Text = ActiveCell.Offset(0, 2)
txtPay.Text = ActiveCell.Offset(0, 3)

End If
End Sub

This code first ensures that you are not trying to move beyond the contents of the data source.  Then it uses a similar approach to the last routine by moving the active cell down one row.  After this active cell has changed it fills in the form controls using the offset property.

Redesigning the Delete Command Button.  Now we want to go one step further and allow the user to actually delete past entries.  The delete operation itself is quite simple, adjusting the active cell and what appears on the form is a bit more complicated.  You can fill in the form with the current entries unless you delete the last entry in the data source.  Since we are using a move the cells up operation after the delete, if you delete the last row you need to move up a row before you fill in the form and accept your active cell.

The code follows and will be discussed in more detail in class.

Private Sub cmdDelete_Click()
Dim CurrentRow As Integer
' Select the row to delete, delete it and shift the rows upward
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp

' Refill controls with current info
' Determine the current row
CurrentRow = Application.ActiveCell.Row
' If you deleted the last row in the data source you need to move up one row
If CurrentRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1 Then

ActiveCell.Offset(-1, 0).Select
cboLocation.Text = ActiveCell
txtDate.Text = ActiveCell.Offset(0, 1)
txtDirector.Text = ActiveCell.Offset(0, 2)
txtPay.Text = ActiveCell.Offset(0, 3)

' If you didn't delete the last row you can use the current row to fill in the form
Else

ActiveCell.Offset(0, 0).Select
cboLocation.Text = ActiveCell
txtDate.Text = ActiveCell.Offset(0, 1)
txtDirector.Text = ActiveCell.Offset(0, 2)
txtPay.Text = ActiveCell.Offset(0, 3)

End If
End Sub

You should feel free to assume that there are many other upgrades we could implement for this form, but hopefully this gives you a realistic sense of some things that can and should be done in such a situation.