VBA Display list in msgbox

[RvdV16681]

Hello,

I'm using a Userform to store incoming andoutgoing equipment. Users of the equipment need to register by filling in theirname. What I would like is that users can see which equipment items they haveused, up till 10 items.
The data is stored on Sheet"Blad1", column A has the equipment number, column B the names ofusers, column C equipment description and column E the date they returned theitem.
On Userform5 is a Textbox, to fill in the name,and a Commandbutton. What I would like is a code that is searching for the namethat is filled is, and a msgbox to pop up showing a list of the last 10 itemsthat are used by this person, so the return so there has to be a value in columnE confirming the item is returned.
I'm now using this code, but that only give thelast item used, and I would like to extend it to the last 10 items.
<!--[if !supportLineBreakNewLine]-->

Private Sub CommandButton1_Click()
Dim I As Long
 Dim Lastrow As Long
 Lastrow = Sheets("Blad1").Cells(Rows.Count, "A").End(xlUp).row
    For I = Lastrow To 1 Step -1
        If Sheets("Blad1").Cells(I, 2).value = TextBox1.value And Sheets("Blad1").Cells(I, 4) > "" Then
              MsgBox "Last item used by " & (Sheets("Blad1").Cells(I, 2).value) & vbNewLine & "is: " & (Sheets("Blad1").Cells(I, 1).value) & ", " & (Sheets("Blad1").Cells(I, 3).value)
            Exit Sub
         End If
        
      Next
 End Sub

Hopefully there is a possibility to change the code and get it to work for me.

Thanks for helping out in advance!!

 

[Trebor76]

Hi RvdV16681,

Untested by try this:

Option Explicit
Private Sub CommandButton1_Click()

    Dim I As Long
    Dim Lastrow As Long
    Dim intCounter As Integer
    Dim strMyList As String
    
    Lastrow = Sheets("Blad1").Cells(Rows.Count, "A").End(xlUp).Row
    
    For I = Lastrow To 1 Step -1
        If Sheets("Blad1").Cells(I, 2).Value = TextBox1.Value And Sheets("Blad1").Cells(I, 4) > "" Then
            intCounter = intCounter + 1
            If intCounter <= 10 Then
                If strMyList = "" Then
                    strMyList = Sheets("Blad1").Cells(I, 1).Value & ", " & Sheets("Blad1").Cells(I, 3).Value
                Else
                    strMyList = strMyList & vbNewLine & Sheets("Blad1").Cells(I, 1).Value & ", " & Sheets("Blad1").Cells(I, 3).Value
                End If
            Else
                Exit For
            End If
        End If
    Next I
    
    MsgBox "Last " & intCounter & " item(s) used by " & TextBox1.Value & vbNewLine & "is: " & strMyList

End Sub

Regards,

Robert

 

[RvdV16681]

Hello Trebor76,

I just tried the code and it works as I want it to.
Thank you for your quick reply and to help me solving it!!

 

[Trebor76]

You're welcome