The Form code below was working correctly, the all of a sudden, it has stopped putting the List values in the right places.
When I click on the submit button
the first list value (.Cells(lastRow, 1).Value = desc) is submited to the correct columns A.
The second list value (.Cells(lastRow, 2).Value = Sheets("List").Cells(TaskType, 1).Value) in colum B is not what I entered in the form
The third list value (.Cells(lastRow, 3).Value = createDate) in column C is correctly placed,
The fourth list value (.Cells(lastRow, 4).Value = Sheets("List").Cells(Collaborators, 1).Value) in column D is in the wrong place
The fifth list value is (.Cells(lastRow, 5).Value = Sheets("List").Cells(Resources, 1).Value) duplicated in column E and in column F
The sixth list value is (.Cells(lastRow, 6).Value = Sheets("List").Cells(priority, 1).Value) is a duplication of what is entered in column E
The seventh list value (.Cells(lastRow, 7).Value = compDate) in column G is correctly placed
and the eigth list value (.Cells(lastRow, 8).Value = Sheets("List").Cells(stages, 1).Value) in column H is bot what I entered in the form.
Can you detect and correct what is causing this problem.


Private Sub CommandButton1_Click()
Application.EnableEvents = False
' Get data from form
Dim desc As String: desc = Me.TaskDescription.Value
Dim TaskType As Long: TaskType = Me.TaskType.ListIndex + 1 ' Adjust for 0-based indexing
Dim createDate As Date: createDate = Me.DateCreated.Value
Dim Collaborators As Long: Collaborators = Me.Collaborators.ListIndex + 1 ' Adjust for 0-based indexing
Dim Resources As Long: Resources = Me.Resources.ListIndex + 1 ' Adjust for 0-based indexing
Dim priority As Long: priority = Me.TaskPriority.ListIndex + 1 ' Adjust for 0-based indexing
Dim compDate As Date: compDate = Me.CompletionDate.Value
Dim stages As Long: stages = Me.StagesRequired.ListIndex + 1 ' Adjust for 0-based indexing

' Calculate difference in days
Dim diffDays As Long: diffDays = DateDiff("d", Date, compDate)

' Determine target sheet based on difference in days
Dim targetSheet As String
Select Case diffDays
Case Is <= 3: targetSheet = "Day"
Case 4 To 9: targetSheet = "Week"
Case 10 To 20: targetSheet = "Forthnight"
Case 21 To 59: targetSheet = "Month"
Case 60 To 89: targetSheet = "QtrYear"
Case 90 To 209: targetSheet = "HalfYear"
Case Else: targetSheet = "Year"
End Select

' Find next available row
Dim lastRow As Long: lastRow = Sheets(targetSheet).Cells(Rows.Count, 1).End(xlUp).Row + 1

' Write data to sheet
With Sheets(targetSheet)
.Cells(lastRow, 1).Value = desc
.Cells(lastRow, 2).Value = Sheets("List").Cells(TaskType, 1).Value
.Cells(lastRow, 3).Value = createDate
.Cells(lastRow, 4).Value = Sheets("List").Cells(Collaborators, 1).Value
.Cells(lastRow, 5).Value = Sheets("List").Cells(Resources, 1).Value
.Cells(lastRow, 6).Value = Sheets("List").Cells(priority, 1).Value
.Cells(lastRow, 7).Value = compDate
.Cells(lastRow, 8).Value = Sheets("List").Cells(stages, 1).Value
End With

' Display confirmation message
MsgBox "Your new task has been recorded in " & targetSheet & ".", vbInformation

' Clear form
Me.TaskDescription.Value = ""
Me.TaskType.ListIndex = -1 ' Set to no selection
Me.DateCreated.Value = ""
Me.Collaborators.ListIndex = -1
Me.Resources.ListIndex = -1
Me.TaskPriority.ListIndex = -1
Me.CompletionDate.Value = ""
Me.StagesRequired.ListIndex = -1
Application.EnableEvents = True
End Sub
Private Sub UserForm_Initialize()
' Run when the form opens
Me.DateCreated.Value = Format(Date, "dd/mm/yy") ' Set to current date directly
End Sub