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

 

[Cyril Beki]

Update is easy, when we offset row 9 on the Data Entries sheet by the selected row index of ListBox1 it will give use the corresponding row number.

However, consider this scenario:

1. You double click a row in ListBox1
2. Then click another row
3. Make a change (this can be step 2 as well, won't matter)
4. Click update

This will result in the wrong row to be updated (row we selected in step 2) instead of the double clicked row.

So we need to devise a safe-guard:

Whenever ListBox is clicked, it should set a boolean variable to false or a hidden checkbox to unchecked.
When ListBox is double clicked it should set the variable to true, or check the checkbox.
When we click Update the code should perform a check on the above and exit update code if false.

My suggestion would be the variable route. What do you say?

What if i just reuse the ADD code and only replace "Datainput.Offset(1, 10).value = strActionBy" to "UbahData.Offset(0, 10).value = strActionBy"

Dim i As Long
Dim strActionBy As String

strActionBy = ""

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

UbahData.Offset(0, 10).value = strActionBy

this should update the data as well right

 

[Gokhan Aycan]

Using the row index would be faster, but whatever works for you.

By the way, how many rows would you expect this listbox to grow to? By default 32.767 is the max. Can be double that Listbox maximum limit. VERY IMPORTANT FOR ME.-VBForums

Try what you wrote above and let me know if you have a problem. I believe it should be easy now.

Edit: Check add, dbl.click and update on all sheets (sections)

 

[Cyril Beki]

Using the row index would be faster, but whatever works for you.

By the way, how many rows would you expect this listbox to grow to? By default 32.767 is the max. Can be double that Listbox maximum limit. VERY IMPORTANT FOR ME.-VBForums

Try what you wrote above and let me know if you have a problem. I believe it should be easy now.

Edit: Check add, dbl.click and update on all sheets (sections)

The code works great, i'm able to update the data as i want. And for the maximum parts, im not sure how much it will grow but for sure i might clean the data when needed to save more space. What do you say ? or you might have any other suggestion when it reach maximum

extra question from me; is it possible to do a line break(new line) inside listbox ?
example; pick some name in listbox -> add button -> the output will show
A
B
C
instead of
A,B,C

 

[Gokhan Aycan]

Making archive copies and cleaning data seems reasonable. It will keep the workbook size manageable as well, less time opening and saving.

 

[Cyril Beki]

Making archive copies and cleaning data seems reasonable. It will keep the workbook size manageable as well, less time opening and saving.

im just wondering is it possible to do a line break(new line) inside listbox ?
example; pick some name in listbox -> add button -> the output will show
A
B
C
instead of
A,B,C

 

[Gokhan Aycan]

I don't think it would be possible without 3rd party stuff.

A google search led me to this for example:

http://www.atoolspro.com/products/bsac-bluesofts-activex-controls.html

 

[Cyril Beki]

I don't think it would be possible without 3rd party stuff.

A google search led me to this for example:

BSAC - Bluesofts ActiveX Controls

I don't think it would be possible without 3rd party stuff.

A google search led me to this for example:

BSAC - Bluesofts ActiveX Controls

So meaning i need to have the 3rd party stuff to get the format as shown in the image as soon as i clicked on Add / Update Button? it produce multiline (B & C) instead of comma (A,B)
p/s: I write it manually, so that you get what i mean

 

[Gokhan Aycan]

On the sheet you can, not in the listbox. But having different delimiters on both would need some work. If you are willing to go that route, maybe best is to ask a new question. Others may not be paying much attention to this one as I am already on it.

 

[Cyril Beki]

On the sheet you can, not in the listbox. But having different delimiters on both would need some work. If you are willing to go that route, maybe best is to ask a new question. Others may not be paying much attention to this one as I am already on it.

alright, Thank you for the help and suggestion, i'll try to post a new question regarding my concern ?