I have set up a userform to act as a cancellation form formy users as part of a trip booking model. Each trip has its own sheet (sheetname is the trip name) upon which a user can enter people’s details to attend.If the person changes their mind, I have set up a Cancellation form for theusers to process this also. This consists of a combo box where the user selectsthe name of the person cancelling their trip, and a listbox which shows thetrips currently booked for the particular person. The user simply selects theperson’s name from the combobox, and then selects the trip they wish to canceland the macro looks for that sheet, finds the users name in the list wishing toattend, then adds “Strike out CANCELLED” to the same row but in Col A, and thetime stamp, user’s name, and a cancellation reason to the same row but in columnsJ, K, L.
Well, that’s how it should be happening. Strangely, themacro is putting the “Strike out CANCELLED” exactly where it should but iswriting the time stamp, user’s name, and cancellation reason to all rows thathave been used for that booking.
<tbody>
[TD="width: 170, bgcolor: transparent"]
Date of Change
[/TD]
[TD="width: 115, bgcolor: transparent"] Team Member's Name [/TD]
[TD="width: 316, bgcolor: transparent"] Cancel Reason [/TD]
[TD="width: 170, bgcolor: transparent"] 11/01/2019 10:41 [/TD]
[TD="width: 115, bgcolor: transparent"] john [/TD]
[TD="width: 316, bgcolor: transparent"] he wants pumpkin [/TD]
[TD="width: 170, bgcolor: transparent"] 11/01/2019 10:41 [/TD]
[TD="width: 115, bgcolor: transparent"] john [/TD]
[TD="width: 316, bgcolor: transparent"] he wants pumpkin [/TD]
[TD="width: 170, bgcolor: transparent"] 11/01/2019 10:41 [/TD]
[TD="width: 115, bgcolor: transparent"] john [/TD]
[TD="width: 316, bgcolor: transparent"] he wants pumpkin [/TD]
[TD="width: 170, bgcolor: transparent"] 11/01/2019 10:41 [/TD]
[TD="width: 115, bgcolor: transparent"] john [/TD]
[TD="width: 316, bgcolor: transparent"] he wants pumpkin [/TD]
</tbody>
This is for a member who appears on the last line, not all 4lines.
What have I done incorrectly?
Well, that’s how it should be happening. Strangely, themacro is putting the “Strike out CANCELLED” exactly where it should but iswriting the time stamp, user’s name, and cancellation reason to all rows thathave been used for that booking.
[TD="width: 115, bgcolor: transparent"] Team Member's Name [/TD]
[TD="width: 316, bgcolor: transparent"] Cancel Reason [/TD]
[TD="width: 170, bgcolor: transparent"] 11/01/2019 10:41 [/TD]
[TD="width: 115, bgcolor: transparent"] john [/TD]
[TD="width: 316, bgcolor: transparent"] he wants pumpkin [/TD]
[TD="width: 170, bgcolor: transparent"] 11/01/2019 10:41 [/TD]
[TD="width: 115, bgcolor: transparent"] john [/TD]
[TD="width: 316, bgcolor: transparent"] he wants pumpkin [/TD]
[TD="width: 170, bgcolor: transparent"] 11/01/2019 10:41 [/TD]
[TD="width: 115, bgcolor: transparent"] john [/TD]
[TD="width: 316, bgcolor: transparent"] he wants pumpkin [/TD]
[TD="width: 170, bgcolor: transparent"] 11/01/2019 10:41 [/TD]
[TD="width: 115, bgcolor: transparent"] john [/TD]
[TD="width: 316, bgcolor: transparent"] he wants pumpkin [/TD]
</tbody>
This is for a member who appears on the last line, not all 4lines.
What have I done incorrectly?
Code:
Sub CancelTrip()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim ws As Worksheet
Dim lItem As Long
Dim wsTripsRg As Range, wsTripsRg2 As Range, rngFoundcancel As Range
Dim ReturnTripSht As Variant, defCancel As Variant, strCancelReason As Variant
Dim strSearchName As String
With Application
.ScreenUpdating = False
End With
CancelQs:
strCancelReason = Application.InputBox("Trip Cancellation?", "Why is the member cancelling their trip?")
If strCancelReason = False Then
defCancel = MsgBox("This trip will NOT be cancelled." & vbNewLine & _
vbNewLine & "Are you sure this is what you want?", vbCritical + vbYesNo)
If defCancel = vbYes Then
Exit Sub
ElseIf defCancel = vbNo Then
GoTo CancelQs
End If
ElseIf strCancelReason = "" Then
MsgBox "You must include a reason if a member cancels a trip", vbCritical, "Warning"
GoTo CancelQs
End If
strSearchName = frmBookings.cboMembName.Value
For lItem = 0 To frmBookings.LbxExistBook.ListCount - 1
If frmBookings.LbxExistBook.Selected(lItem) = True Then
Set wsTripsRg = Worksheets("Trips").Range("TripDate")
Set wsTripsRg2 = Worksheets("Trips").Range("TripSheetName")
'MsgBox (frmBookings.LbxExistBook.List(lItem))
ReturnTripSht = Application.Index(wsTripsRg2, Application.Match(frmBookings.LbxExistBook.List(lItem), wsTripsRg, 0))
With Sheets(ReturnTripSht)
'.Select
'Set the first and last row to loop through
Firstrow = 5
Lastrow = .Cells(.Rows.Count, "c").End(xlUp).Row
'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'We check the values in the A column in this example
With .Cells(Lrow, "c")
If Not IsError(.Value) Then
If .Value = strSearchName Then .Offset(0, -2).Value = "Strike out CANCELLED" 'this works
.Offset(0, 10).Value = frmBookings.txtLogDate.Text 'this writes to the correct row and all rows above
.Offset(0, 11).Value = frmBookings.txtTmMember.Text 'this writes to the correct row and all rows above
.Offset(0, 12).Value = strCancelReason 'this writes to the correct row and all rows above
End If
End With
Next Lrow
End With
End If
Next lItem
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub