How to update a listbox by using a sub procedure

Question

I have a user form and a listbox on that, I am using the listbox as activity memo, I developed a sub for updating that listbox and also a listobject in a sheet as its bank. (the listbox just have the current session logs, but list object have all the records). I can't pass the listbox control to the sub. I tried it in a module and also in the userform code section, but I can't refer listbox name (not as a string nor as a listbox control). Do you have any idea or sollution for this? Here is my code.

Public Sub memoadd(ByVal txt As String, ByVal tablename As String, ByVal listbox As String)
    Dim lrow As ListRow, lobj As ListObject, lstbox As listbox

    Set lobj = log.ListObjects(tablename)
    Set lrow = lobj.ListRows.Add(1, True)
    Set lstbox = Me.Controls(listbox)
    lstbox.AddItem txt & " @ " & Format(Time, "hh:mm"), "0"

    With lrow
       lrow.Range(1, 1) = lobj.ListRows.Count
       lrow.Range(1, 2) = Now
       lrow.Range(1, 3) = lstbox.List(0)
       lrow.Range(1, 4) = Environ$("Computername")
       lrow.Range(1, 5) = Environ$("username")
    End With

    lstbox.ListIndex = 0
End Sub

Here is how I am using that in my procedures.

memoadd "master file of A/C: " & ac & " is opened", "Logs", "ListBox2"

I also tried putting the userform name instead of me, but no luck — Mahhdy
The reason your code fails is Dim lstbox as listbox should be Dim lstbox as MSForms.ListBox. ListBox and MSForms.ListBox are not the same type — chris neilsen

L42 L42 · Accepted Answer · 2018-02-14T23:43:40

First, you have to (make sure) access the ListBox at runtime.
Meaning the Userform is already loaded when you call the Sub.

Another suggestion in your Sub, instead of passing ListBox as String, pass it as MSForms.ListBox Object. Something like this:

Public Sub memoadd(ByVal txt As String, _
                   ByVal tablename As String, _
                   ByVal listbox As MSForms.ListBox) 

listbox.AddItem txt & " @ " & Format(Time, "hh:mm"), "0"
'/* no need to set lobj here, you can use listbox directly */
'/* rest of your code */

End Sub

I hope this gets you going, you didn't mention other specifics regarding your code.
And so I didn't check and assumed it should work.

How to update a listbox by using a sub procedure

1 Answers