Listbox Multiselect

[Cyril Beki]

Hello expert,

I have 2 Listboxs (txtActionby & Listbox1), i would like to do a multiselect in txtActionby separated by comma and will be displayed in the Listbox1 once the Add button is pressed.
And whenever i want to update the data, the previous multiselected person separated by comma will be highlighted in the txtActionby. How can i do that ? i Appreaciate all your help. Thanks in advance

Download the file here:

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

 

[Gokhan Aycan]

Assuming txtActionBy (not a very good name for a listbox imho ) is 1 columns:

Dim i as Long
Dim strActionBy as String

strActionBy = ""

For i = 0 to txtActionBy.ListCount - 1
    If txtActionBy.Selected(i) Then strActionBy = strActionBy & ", "    ' There is a space after comma for readability
Next i

Assign strActionBy to the appropriate column of ListBox1.

To retrieve back the selected items:

Dim strActionBy as String

strActionBy = <read the value from ListBox1>

Dim arr as Variant
Dim elem as Variant

arr = Split(strActionBy, ", ")

Dim i as Long

For Each elem in arr
    For i = 0 to txtActionBy.ListCount - 1
        If elem = txtActionBy.List(i) Then txtActionBy.Selected(i)
        Exit For
    Next i
Next elem

 

[Cyril Beki]

Assuming txtActionBy (not a very good name for a listbox imho ) is 1 columns:

Dim i as Long
Dim strActionBy as String

strActionBy = ""

For i = 0 to txtActionBy.ListCount - 1
    If txtActionBy.Selected(i) Then strActionBy = strActionBy & ", "    ' There is a space after comma for readability
Next i

Assign strActionBy to the appropriate column of ListBox1.

To retrieve back the selected items:

Dim strActionBy as String

strActionBy = <read the value from ListBox1>

Dim arr as Variant
Dim elem as Variant

arr = Split(strActionBy, ", ")

Dim i as Long

For Each elem in arr
    For i = 0 to txtActionBy.ListCount - 1
        If elem = txtActionBy.List(i) Then txtActionBy.Selected(i)
        Exit For
    Next i
Next elem

Hello sir, thank you for replying, where do i need to put the code anyway ?

 

[Gokhan Aycan]

The first one should be inside "Add" button code.

Second one should perhaps be inside ListBox1 click event, or "Update" button. Depends on your design.

Just to be sure: You want to be able to select multiple "Action Taken By" (people?) items same time, right?

 

[Cyril Beki]

The first one should be inside "Add" button code.

Second one should perhaps be inside ListBox1 click event, or "Update" button. Depends on your design.

Just to be sure: You want to be able to select multiple "Action Taken By" (people?) items same time, right?

Reply to your question:
The Action taken by is people, and is multiselect. The function should be as below;
1. I pick one or several people from txtActionby and will be displayed in listbox1 (Separated by comma when i choose more than 1 people) once i click Add button
2. If i feel i want to update the data, i just double click on the data inside listbox1. The txtActionby should highlight the previous multiselected people and i can update the people as well

 

[Gokhan Aycan]

You have to write the code to read the selected row of ListBox1 for the correct column. Something like:

strActionBy = ListBox1.List(ListBox1.ListIndex, column_no)

you have to substitute the "column_no" with the "Action Taken By" column's number within ListBox1.

Should have been explicit about it.

 

[Cyril Beki]

You have to write the code to read the selected row of ListBox1 for the correct column. Something like:

strActionBy = ListBox1.List(ListBox1.ListIndex, column_no)

you have to substitute the "column_no" with the "Action Taken By" column's number within ListBox1.

Should have been explicit about it.

Thank you for replying sir, i already write column no code as suggested by you sir, but i get this kind of error when i double click it for updating purpose in the listbox1(refer image)

as well as, when i picked several from txtActionby, it doesnt show up in the listbox. (refer image)

 

[Cyril Beki]

Thank you for replying sir, i already write column no code as suggested by you sir, but i get this kind of error when i double click it for updating purpose in the listbox1(refer image)

as well as, when i picked several from txtActionby, it doesnt show up in the listbox. (refer image)

 

[Gokhan Aycan]

Sorry, that part should be:

If elem = txtActionBy.List(i) Then txtActionBy.Selected(i) = True

 

[Cyril Beki]

Sorry, that part should be:

If elem = txtActionBy.List(i) Then txtActionBy.Selected(i) = True

It still doesnt show up the separated data sir. Still blank

nk