Listbox Multiselect

Cyril Beki

New Member
Joined
Sep 18, 2021
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
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:
Multiselect.PNG
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows
Assuming txtActionBy (not a very good name for a listbox imho :) ) is 1 columns:

VBA Code:
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:

VBA Code:
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

New Member
Joined
Sep 18, 2021
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
Assuming txtActionBy (not a very good name for a listbox imho :) ) is 1 columns:

VBA Code:
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:

VBA Code:
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

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 18, 2021
Messages
41
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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?
syntax.PNG


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

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows
:) 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

New Member
Joined
Sep 18, 2021
Messages
41
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

:) 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)
 

Attachments

  • invalid.PNG
    invalid.PNG
    23.9 KB · Views: 9

Cyril Beki

New Member
Joined
Sep 18, 2021
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
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)
separated.PNG
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows
Sorry, that part should be:

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

Forum statistics

Threads
1,144,424
Messages
5,724,249
Members
422,542
Latest member
jedidia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top