Listbox Multiselect Add text to last column in same row

[H Moller]

This is my first request on this forum.
I cannot find this seemingly simple answer.
I have a multiselect listbox (serlib) and textbox( fnbx) on a userform.
The Commandbutton has vba that search the reference number(which is in the textbox) down column A, and if found, populates the Listbox with the appropriate results.
The listbox has 9 columns.

I need to do the following:
After making a selection(s) in the listbox, and with another Commandbutton, the word "paid" needs to be written into the last column of the found row on the sheet.

I was trying to have the reference number on the sheet selected, and write "Paid" to activecell.offset(0,9), but it does not work.
I also tried the following:
If .Selected(cntr) Then serlib.List(0, 9) = "PAID"
End If
But that only changes the Listbox content and not the needed cell.
Please help.

Here is my code on the find portion:

Private Sub CommandButton2_Click()


With Worksheets(1).Range("A1:A2000")

Set c = .Find(What:=fnbx, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)


If Not c Is Nothing Then
firstaddress = c.Address
Do
serlib.AddItem c.Value


'.List c.Offset(0, 1)
serlib.List(serlib.ListCount - 1, 1) = c.Offset(0, 1)
serlib.List(serlib.ListCount - 1, 2) = c.Offset(0, 2)
serlib.List(serlib.ListCount - 1, 3) = c.Offset(0, 3)
serlib.List(serlib.ListCount - 1, 4) = c.Offset(0, 4)
serlib.List(serlib.ListCount - 1, 5) = c.Offset(0, 5)
serlib.List(serlib.ListCount - 1, 6) = c.Offset(0, 6)
serlib.List(serlib.ListCount - 1, 7) = c.Offset(0, 7)
serlib.List(serlib.ListCount - 1, 8) = c.Offset(0, 8)
serlib.List(serlib.ListCount - 1, 9) = c.Offset(0, 9)

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With


End Sub

Here is something I thought would work, but I cannot complete the code:

Private Sub CommandButton3_Click()
Dim i As Long, x
With Me.serlib
For i = 0 To .ListCount - 1
If .Selected(i) Then
'this is wrong!!!
Selected(i).List(0, 9) = "PAID"
Exit Sub
End If
Next
End With
End Sub

 

[My Aswer Is This]

Since this question has not had any takers in the last 24 Hrs. I will take a shot at it.

Why do we need a textbox a multicolumn listbox and two command buttons to just find a certain word and enter "Paid" adjacent to it? Why not one textbox and one command button.

Tell me what column to search and what column to put "Paid" in and the name of the textbox.

 

[H Moller]

Thank you for your reply.

The textbox has a reference number, referring to a certain client. The first command button runs the search (find) down column A and if found it populates the Listbox with the reference number and the adjacent cells.
The adjacent cells obviously has the clients details, tasks, amounts etc. (columns B to I)

I need to have the listbox for me to decide which of the rows,( which represents clients details, tasks, amounts) are paid for.
Once that is decided and selected the second command button will cause to have the word "Paid" in the last column. "Paid must be written in Column J, on the same row as the selected items.

Your help is much appreciated.
Regards

 

[My Aswer Is This]

Try this:

This script should do what you want without the need of a listbox.
The script searches column "A" for the value in Textbox1.
When found it puts "Paid" in column "J"

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
ans = TextBox1.Value
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        If Cells(i, 1).Value = ans Then Cells(i, 10).Value = "Paid": Exit Sub
    Next

Application.ScreenUpdating = True

End Sub

 

[H Moller]

I found code that works:
After the Listbox is populated, I can now select any row(s) and it writes "Paid" to the same row in column J




Private Sub CommandButton1_Click()




Dim rRange As Range
Dim lCount As Long 'Counter


On Error GoTo ErrorHandle




Set rRange = Range("J1")
.
With serlib
For lCount = 0 To .ListCount - 1
If .Selected(lCount) = True Then

rRange.Offset(lCount, 0).Value = "PAID"
End If
Next
End With


BeforeExit:
Set rRange = Nothing




Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit


End Sub

 

[My Aswer Is This]

Well glad you found code some where else that works for you.
What was wrong with the code I provided?

 

[H Moller]

I really needed the listbox, because it consists of at least 7 rows at a time.
Only some of those rows are "paid".
Your code did not give me a choice as I could only populate the textbox

Thanks anyway
Regards