Previous Task and Next Task buttons aren't working

**top.C.Crets** · 09-06-2012, 07:49 PM

Hello,

I am have the attached worksheet and userform. I am looking to get the Previous Task and Next Task command buttons functioning properly. At the moment, when I hit them, they clear all of the text/combo boxes. I will also add the code to the bottom of this post for convenience. I guess I must add that when the Current Status has been updated to Complete, it copies that row, deletes it, and pastes it to the next available row on the Completed Tasks sheet. Not sure if that will have any factor.

CSI Team Maintenance Tasks.xlsm

Code for the userform:

Option Explicit

Dim Where As Range
Dim LastFind As Range



Private Sub UserForm_Initialize()
  'Setup the Tag property for easier access
  taskNum.Tag = "A"
  taskDesc.Tag = "B"
  notes.Tag = "C"
  logDate.Tag = "D"
  reqCompDate.Tag = "E"
  curStatus.Tag = "F"
  statAuth.Tag = "G"
  lstUpBy.Tag = "H"
  actCompDate.Tag = "I"
    
  'Setup a range to search
  Set Where = Worksheets("Current Tasks").Columns("A")
End Sub

Private Sub ClearForm()
  'Clears every control that has a Tag property
  Dim C As Control
  For Each C In Me.Controls
    If C.Tag <> "" Then C.Value = ""
  Next
  Set LastFind = Nothing
End Sub

Private Sub FillForm()
  'Fills every control that has a Tag property
  Dim C As Control
  For Each C In Me.Controls
    If C.Tag <> "" Then
      'Fill the textbox from the column specified by the Tag property
      C.Value = Intersect(LastFind.EntireRow, LastFind.Parent.Columns(C.Tag))
    End If
  Next
End Sub

Private Sub SaveForm()
  'Copy the data to the database
  Dim C As Control
  For Each C In Me.Controls
    If C.Tag <> "" Then
      Intersect(LastFind.EntireRow, LastFind.Parent.Columns(C.Tag)) = C.Value
    End If
  Next
End Sub

Private Sub cbFindNext_Click()
  'Commandbutton "FindNext"
  If LastFind Is Nothing Then
    Set LastFind = Where.Find(Me.taskNum, LookIn:=xlValues, LookAt:=xlPart)
  Else
    Set LastFind = Where.FindNext(LastFind)
  End If
  If LastFind Is Nothing Then ClearForm Else FillForm
End Sub

Private Sub cbFindPrev_Click()
  'Commandbutton "FindPrevious"
  If LastFind Is Nothing Then
    Set LastFind = Where.Find(Me.taskNum, LookIn:=xlValues, LookAt:=xlPart)
  Else
    Set LastFind = Where.FindPrevious(LastFind)
  End If
  If LastFind Is Nothing Then ClearForm Else FillForm
End Sub

Private Sub cmdAdd_Click()

  If LastFind Is Nothing Then
    'find first empty row in database
Set LastFind = Where.Cells(Where.Cells.Count).End(xlUp).Offset(1)
  End If

'check for a task number
If Trim(Me.taskNum.Value) = "" Then
  Me.taskNum.SetFocus
  MsgBox "Please enter a task number"
  Exit Sub
End If

'check for a description
If Trim(Me.taskDesc.Value) = "" Then
  Me.taskDesc.SetFocus
  MsgBox "Please enter a task description"
  Exit Sub
End If

'check for a log date
If Trim(Me.logDate.Value) = "" Then
  Me.logDate.SetFocus
  MsgBox "Please enter a log date"
  Exit Sub
End If

'check for a completion date
If Trim(Me.reqCompDate.Value) = "" Then
  Me.reqCompDate.SetFocus
  MsgBox "Please enter the requested completion date"
  Exit Sub
End If

'check for a Status
If Trim(Me.curStatus.Value) = "" Then
  Me.curStatus.SetFocus
  MsgBox "Please enter the current status"
  Exit Sub
End If

'check for a Status Author
If Trim(Me.statAuth.Value) = "" Then
  Me.statAuth.SetFocus
  MsgBox "Please enter a task author"
  Exit Sub
End If

'check for Last Updated By
If Trim(Me.lstUpBy.Value) = "" Then
  Me.statAuth.SetFocus
  MsgBox "Please enter who updated this task last"
  Exit Sub
End If
  
  SaveForm
  ClearForm
End Sub

Private Sub cmdClose_Click()
  Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the button!"
  End If
End Sub