VBA loop through selected rows in listbox in userform, do something when column value in selected row is equal to

  • Below code only saves the date to the sheet for only 1 of the selected rows. I do not understand why other selected rows do not get the date value.


    Sub SaveDateLastSend() Dim X As Integer
    Dim iRow As Long 'To store the row number available in column T in sheet Log

    'run for the loop
    With frmFormLV.lstLogBook
    For X = 0 To .ListCount - 1
    If .Selected(X) Then
    iRow = .Column(19, X)
    Worksheets(“Log").Cells(iRow, 15).Value = Date
    End If
    Next X
    End With
    End sub


    When debugging by returning the variable iRow in a messagbox only 1 variable is returned when the coding is like this:


    'run for the loop
    With frmFormLV.lstLogBook
    For X = 0 To .ListCount - 1
    If .Selected(X) Then
    iRow = .Column(19, X)
    MsgBox iRow
    Worksheets(“Log").Cells(iRow, 15).Value = Date
    End If
    Next X
    End With
    End sub


    But when I comment out the line to where the date is saved, all the variables are returned in messageboxes. So the loop should be fine I think.


    'run for the loop
    With frmFormLV.lstLogBook
    For X = 0 To .ListCount - 1
    If .Selected(X) Then
    iRow = .Column(19, X)
    MsgBox iRow
    'Worksheets(“Log").Cells(iRow, 15).Value = Date

    End If

    Next X

    End With

    End sub


    But it's not because only the first selected row gets the date returned. Why not the other rows? Hopefully someone can help me since I just started writing codes.



  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • Thanks for pointing me the forum rules.


    Here is my issue


    Below code only saves the date to the sheet for only 1 of the selected rows. I do not understand why other selected rows do not get the date value.



    When debugging by returning the variable iRow in a messagbox only 1 variable is returned when the coding is like this:



    But when I comment out the line to where the date is saved, all the variables are returned in messageboxes. So the loop should be fine I think.

    But it's not because only the first selected row gets the date returned. Why not the other rows? Hopefully someone can help me since I just started writing codes.

  • Hi Roy, Thank you for your interest to help me. It was late yesterday so I couldn't slim the file down to it's essential.


    Basically I want to select multiple rows with the listbox on an userform and save the date to the sheet . To identify the right row I have a helper column. Somehow my loop only returns for one row the date to the right column. With msgbox as a method to debug, all the values of variable iRow are returned. So I am a little lost where it goes wrong. I hope someone can point me to the right directions to solve the issue. Thanks in advance for all the help.

  • Why put the code in a Standard Module not the UserForm?


    Why do you need a Helper Column?


    Try this. It stores the selected items' ListIndex in an array then uses the array to determine the correct rows.

  • Many thanks for your time to make this work Roy! I need to learn more about the use of arrays I guess. There is one flaw in the coding I've noticed. The date shows up always up on the second row regardless whether it is selected. Is there a quick fix?


    Suppose if I would add another command button to move selected multiple lines to another sheet or delete selected multiple lines from the sheet what would it look like? To move/delete one line is one thing. But multiple lines is still a mystery to me.


    Originally I was not planning to use userforms in Excel because it is new to me and it seems more difficult/more work to me than to build the application without userforms. This is why the code is in the standard module so the other macro's that have been written can call this macro as well. The use of a helper column is actually to compensate my VBA knowledge I guess! Again many thanks Roy for your efforts. Thumbs up!

  • I've made time to add some quick code that fixes the problem and also shows how to delete rows and move to another sheet.


    I'll check back tomorrow.

  • Once again thank you for your time to look into this. I was at a dead end with my coding. But now it starts to look much better. I got to master this array thing someday. It looks a little abstract to me for now.


    Your revised code does not return the date anymore to row 2 but it jumps to debug mode on below line after running.

    Code
    1. shLog.Cells(CInt(arr(X)), 15) = Format(Date, "dd/mm/yyyy")

    The delete selected lines macro works superb!


    The move selected lines macro copy the lines to another sheet but the selected lines in the original sheet Log are still there. Perhaps you ran out of time to finish it - It's already impressive what you have accomplished in such a little time.

    What would the extra code be like when a string (for example "completed") has to be stored in column Notes for each line that has been moved to sheet Move To?