writing from userform to sheets - overwriting rows above

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,053
Office Version
  1. 365
Platform
  1. Windows
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?

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

 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Code:
                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 If
 
  • Like
Reactions: ajm
Upvote 0
Code:
                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 If

something so simple! many thanks.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top