In this VBA code for my form, it is having diffuclty accurately reading the values from…
- User
In this VBA code for my form, it is having diffuclty accurately reading the values from the List sheet and placing the values in the correct columns. Can you see any reason in the code that 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 Application.Wait (Now + TimeValue("0:00:02")) ' 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 Application.Wait (Now + TimeValue("0:00:01")) ' 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 Unload Me ' Unload the form Application.EnableEvents = True End Sub
- Assistant
Based on the provided code, it appears that the issue might be related to the indexing when retrieving values from the "List" sheet.
In the code, TaskType, Collaborators, Resources, priority, and stages variables are being used as indexes to retrieve values from the "List" sheet. However, these variables are calculated by adding 1 to the ListIndex property of respective form controls, which might not always correspond to the actual row number in the "List" sheet.
To fix this issue, you can adjust the indexing by subtracting 1 from each variable when retrieving the values from the "List" sheet. Here's the modified code:
With Sheets(targetSheet) .Cells(lastRow, 1).Value = desc .Cells(lastRow, 2).Value = Sheets("List").Cells(TaskType - 1, 1).Value .Cells(lastRow, 3).Value = createDate .Cells(lastRow, 4).Value = Sheets("List").Cells(Collaborators - 1, 1).Value .Cells(lastRow, 5).Value = Sheets("List").Cells(Resources - 1, 1).Value .Cells(lastRow, 6).Value = Sheets("List").Cells(priority - 1, 1).Value .Cells(lastRow, 7).Value = compDate .Cells(lastRow, 8).Value = Sheets("List").Cells(stages - 1, 1).Value End WithBy subtracting 1 from the index variables, we adjust for the 0-based indexing in VBA. This should ensure that the correct values are retrieved from the "List" sheet and placed in the correct columns.
- User
When I run the Form code below, The Task Description is written to column A which is correct The Task Type is written to column B which is correct The Date Created is written to column C which is correct Column D does not have the correct value, the Task Type vale is repaeated in column D Column E has the header Task Type written into the cell and 'Task Type' is not part of the values in the list Column F contains a list vale from Task Type The Completion Date is written to column G which is correct Column H has the header Task Type written into the cell and 'Task Type' is not part of the values in the list
Option Explicit Private Sub CommandButton1_Click() Application.EnableEvents = False ' Get data from form Dim TaskDescription As String: TaskDescription = Me.TaskDescription.Value Dim TaskType As Long: TaskType = Me.TaskType.ListIndex + 1 ' Adjust for 0-based indexing Dim DateCreated As Date: DateCreated = 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 TaskPriority As Long: TaskPriority = Me.TaskPriority.ListIndex + 1 ' Adjust for 0-based indexing Dim CompletionDate As Date: CompletionDate = Me.CompletionDate.Value Dim StagesRequired As Long: StagesRequired = Me.StagesRequired.ListIndex + 1 ' Adjust for 0-based indexing
' Calculate difference in days Dim diffDays As Long: diffDays = DateDiff("d", Date, CompletionDate)
' 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 Application.Wait (Now + TimeValue("0:00:01")) ' Write data to sheet With Sheets(targetSheet) .Cells(lastRow, 1).Value = TaskDescription .Cells(lastRow, 2).Value = Sheets("List").Cells(TaskType - 1, 1).Value .Cells(lastRow, 3).Value = DateCreated .Cells(lastRow, 4).Value = Sheets("List").Cells(Collaborators - 1, 1).Value .Cells(lastRow, 5).Value = Sheets("List").Cells(Resources - 1, 1).Value .Cells(lastRow, 6).Value = Sheets("List").Cells(TaskPriority - 1, 1).Value .Cells(lastRow, 7).Value = CompletionDate .Cells(lastRow, 8).Value = Sheets("List").Cells(StagesRequired - 1, 1).Value End With Application.Wait (Now + TimeValue("0:00:01")) ' 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 Unload Me ' Unload the form 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
- Assistant
It seems that there is an issue with the indexing when retrieving values from the "List" sheet. Instead of subtracting 1 from the index variables, as mentioned in the previous response, you need to subtract 2 to account for the header row in the "List" sheet.
Here's the modified code with the correct indexing:
With Sheets(targetSheet) .Cells(lastRow, 1).Value = TaskDescription .Cells(lastRow, 2).Value = Sheets("List").Cells(TaskType - 2, 1).Value .Cells(lastRow, 3).Value = DateCreated .Cells(lastRow, 4).Value = Sheets("List").Cells(Collaborators - 2, 1).Value .Cells(lastRow, 5).Value = Sheets("List").Cells(Resources - 2, 1).Value .Cells(lastRow, 6).Value = Sheets("List").Cells(TaskPriority - 2, 1).Value .Cells(lastRow, 7).Value = CompletionDate .Cells(lastRow, 8).Value = Sheets("List").Cells(StagesRequired - 2, 1).Value End WithBy subtracting 2 from the index variables, you take into account the header row in the "List" sheet, and the correct values should be placed in the corresponding columns now.
- User
Error: Application-defined or object-defined error, on the line:.Cells(lastRow, 2).Value = Sheets("List").Cells(TaskType - 2, 1).Value
- Assistant
Apologies for the confusion caused. To resolve the error, you need to modify the indexing to account for the header row in the "List" sheet correctly.
Here's the updated code with the correct indexing:
With Sheets(targetSheet) .Cells(lastRow, 1).Value = TaskDescription .Cells(lastRow, 2).Value = Sheets("List").Cells(TaskType + 1, 1).Value .Cells(lastRow, 3).Value = DateCreated .Cells(lastRow, 4).Value = Sheets("List").Cells(Collaborators + 1, 1).Value .Cells(lastRow, 5).Value = Sheets("List").Cells(Resources + 1, 1).Value .Cells(lastRow, 6).Value = Sheets("List").Cells(TaskPriority + 1, 1).Value .Cells(lastRow, 7).Value = CompletionDate .Cells(lastRow, 8).Value = Sheets("List").Cells(StagesRequired + 1, 1).Value End WithBy adding 1 to the index variables (TaskType, Collaborators, Resources, TaskPriority, and StagesRequired), we properly account for the header row in the "List" sheet. This should resolve the
Application-defined or object-defined errorand place the correct values from the "List" sheet in the respective columns. - User
only the values in column A B C & G are correct
- Assistant
I apologize for the confusion. Let's try a different approach to retrieve the values from the "List" sheet and ensure they are placed correctly in the target sheet.
Since you are using the ListIndex property to retrieve the selected values from the ComboBoxes, you can directly get the corresponding values from the "List" sheet using the ListIndex. You don't need to adjust the index or account for the header row.
Here's the modified code:
With Sheets(targetSheet) .Cells(lastRow, 1).Value = TaskDescription .Cells(lastRow, 2).Value = Me.TaskType.Value .Cells(lastRow, 3).Value = DateCreated .Cells(lastRow, 4).Value = Me.Collaborators.Value .Cells(lastRow, 5).Value = Me.Resources.Value .Cells(lastRow, 6).Value = Me.TaskPriority.Value .Cells(lastRow, 7).Value = CompletionDate .Cells(lastRow, 8).Value = Me.StagesRequired.Value End WithIn this updated code, we are directly assigning the values from the ComboBoxes to the respective cells in the target sheet. This ensures that the correct selected values from the ComboBoxes are written to the target sheet.
Please give this code a try, and it should accurately place the values in the correct columns in the target sheet.
- User
Thanks it worked
- Assistant
You're welcome! I'm glad that the updated code worked for you and the values are now correctly placed in the target sheet. If you have any further questions or need additional assistance, feel free to ask. Happy coding!