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]

Just a note, please don't call me "sir", I feel strange.

 

[Cyril Beki]

Just noticed you don't qualify the Range object, best is to reference the worksheet as well. i.e., Worksheets("<name_here>").Range(...)... Now, this would not be very efficient, and we usually create a worksheet object and set it to the worksheet and use that as a reference, so the code is much more readable and easier to write.

' Declare the variable
Dim ws as Worksheet

' Set the object
Set ws = Worksheets("<name_here")

' Use the variable
ws.Range(...)... = ...

' Clean up set variable
Set ws = Nothing

Back to the topic: So you are using this part to populate ListBox1.

With Range("A9:P" & lr)
    Me.ListBox1.RowSource = .Address
End With

Therefore you must change the cell value within that range that correspond to ActionTakenBy for this row.

I think i'm lost already . *sigh

 

[Cyril Beki]

I think i'm lost already . *sigh

Add Button

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
With Range("K9" & lr)
    Me.ListBox1.RowSource = .Address
End With

Listbox Double Click

Dim strActionBy As String

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

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) = True
        Exit For
    Next i
Next elem

Update Button

Dim strActionBy As String

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

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) = True
        Exit For
    Next i
Next elem

Anything i need to change from this code ? I really lost. Sorry

 

[Gokhan Aycan]

I am examining the code in more detail. I may have some pointers later on (I am occupied trying to repair my headphones), so this becomes more maintainable in the future.

Here is where you enter ActionTakenBy into DataEntries sheet inside Add_Click event code.

'Action By
Datainput.Offset(1, 10).value = Me.txtActionby.value

Now this won't work because it is multiselect. Therefore we must have the value of strActionBy instead of Me.txtActionBy.Value

'Action By
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

Datainput.Offset(1, 10).value = strActionBy

Then when you assign ListBox1 values from RowSource it should be there.

Let me know if this part works.

 

[Cyril Beki]

I am examining the code in more detail. I may have some pointers later on (I am occupied trying to repair my headphones), so this becomes more maintainable in the future.

Here is where you enter ActionTakenBy into DataEntries sheet inside Add_Click event code.

'Action By
Datainput.Offset(1, 10).value = Me.txtActionby.value

Now this won't work because it is multiselect. Therefore we must have the value of strActionBy instead of Me.txtActionBy.Value

'Action By
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

Datainput.Offset(1, 10).value = strActionBy

Then when you assign ListBox1 values from RowSource it should be there.

Let me know if this part works.

I already write the code as you suggested and the result i shown in the image. It works but it only show comma without picked name

'Action By
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

Datainput.Offset(1, 10).value = strActionBy

 

[Gokhan Aycan]

Another mistake on my part, forgot to add listbox item to strActionBy

Instead of:

If txtActionby.Selected(i) Then strActionBy = strActionBy & ", "

Paste this one:

If txtActionby.Selected(i) Then strActionBy = IIf(strActionBy = "", txtActionby.List(i), strActionBy & ", " & txtActionby.List(i)) ' There is a space after comma for readability

 

[Cyril Beki]

Another mistake on my part, forgot to add listbox item to strActionBy

Instead of:

If txtActionby.Selected(i) Then strActionBy = strActionBy & ", "

Paste this one:

If txtActionby.Selected(i) Then strActionBy = IIf(strActionBy = "", txtActionby.List(i), strActionBy & ", " & txtActionby.List(i)) ' There is a space after comma for readability

View attachment 47211

It works, what about the Update button and Listbox Double Click ?

 

[Gokhan Aycan]

Great, Double Click first then:

Remove the line where: Me.txtActionBy.Value = Me.ListBox1.Column(10)

and paste this:

' Clear previous selections
Me.txtActionby.MultiSelect = fmMultiSelectSingle
Me.txtActionby.Value = ""
Me.txtActionby.MultiSelect = fmMultiSelectMulti

Dim i As Long
Dim strActionBy As String
Dim arr As Variant
Dim elem As Variant

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

arr = Split(strActionBy, ", ")

For Each elem In arr
    For i = 0 To txtActionby.ListCount - 1
        If elem = txtActionby.List(i) Then
            txtActionby.Selected(i) = True
            Exit For
        End If
    Next i
Next elem

 

[Cyril Beki]

Great, Double Click first then:

Remove the line where: Me.txtActionBy.Value = Me.ListBox1.Column(10)

and paste this:

' Clear previous selections
Me.txtActionby.MultiSelect = fmMultiSelectSingle
Me.txtActionby.Value = ""
Me.txtActionby.MultiSelect = fmMultiSelectMulti

Dim i As Long
Dim strActionBy As String
Dim arr As Variant
Dim elem As Variant

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

arr = Split(strActionBy, ", ")

For Each elem In arr
    For i = 0 To txtActionby.ListCount - 1
        If elem = txtActionby.List(i) Then
            txtActionby.Selected(i) = True
            Exit For
        End If
    Next i
Next elem

It works like charm, and what about the update button ?

 

[Gokhan Aycan]

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?