Select Multiple Items to Text Box

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Select Multiple Items to Text Box

Post by adam »

I need insert multiple selected items from ListBox to TextBox and if I deselect some item, it will be deleted from the text box. following is the code I'm trying to use. But it's giving me the error variable not defined. Any help would be appreciated.

Code: Select all

Private Sub Test_Click()
myVar = ""
    
    For X = 0 To Me.Test.ListCount - 1
        If Me.Test.Selected(X) Then
            If myVar = "" Then
                myVar = Me.Test.List(X, 0)
            Else
                myVar = myVar & "," & Me.Test.List(X, 0)
            End If
        End If
    Next X
    Me.txt2.Value = myVar
    End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select Multiple Items to Text Box

Post by HansV »

Try declaring the variables myVar and X.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Select Multiple Items to Text Box

Post by adam »

Code: Select all

Dim listItems As String, i As Long
    
    With Test
        For i = 0 To .ListCount - 1
            If .Selected(i) Then listItems = listItems & .List(i) & ", "
        Next i
    End With
    
    Me.txt1.Value = Left(listItems, Len(listItems) - 2)
This code works for me. But it sends column 0 values to textbox. I have two columns in my list box where I want the column 1 values to be sent to textbox. How can I achieve this?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select Multiple Items to Text Box

Post by HansV »

Change .List(i) to .List(i, 1)
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Select Multiple Items to Text Box

Post by adam »

How could I place the line

Code: Select all

if txt1.Value <> "" then
line

so that when the user double click any listbox item;
1. the code would see if the textbox is empty, if the textbox is empty, the code would place the double clicked item in text box. and after that when the user again double clicks the code would place the second item with a ", " in the textbox and so on.

Code: Select all

Private Sub Test_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim listItems As String, i As Long
    
    With Dim listItems As String, i As Long
    
    With Test
        For i = 0 To .ListCount - 1
            If .Selected(i) Then listItems = listItems & .List(i, 1) & ", "
        Next i
    End With
    
    Me.txt1.Value = Left(listItems, Len(listItems) - 2)
   End Sub 
I hope I've made my question clear.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select Multiple Items to Text Box

Post by HansV »

You can double-click only one item at a time, so you don't have to loop through the items.

Code: Select all

Private Sub Test_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim s As String
    s = Me.Test.List(Me.Test.ListIndex, 1)
    If Me.txt1 = "" Then
        Me.txt1 = s
    Else
        Me.txt1 = Me.txt1 & ", " & s
    End If
End Sub
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Select Multiple Items to Text Box

Post by adam »

Thankyou so much Hans. It worked really well.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Select Multiple Items to Text Box

Post by adam »

if I want two columns to be copied? this modification is not working.

Code: Select all

s = Me.Test.List(Me.Test.ListIndex, 0, Me.Test.ListIndex, 1)
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select Multiple Items to Text Box

Post by HansV »

Of course not, that makes no sense. Perhaps you want

s = Me.Test.List(Me.Test.ListIndex, 0) & " " & Me.Test.List(Me.Test.ListIndex, 1)
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Select Multiple Items to Text Box

Post by adam »

Thankyou so much Hans. It worked realy well.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Select Multiple Items to Text Box

Post by adam »

I've tried the following line to remove text that already exists in the textbox when the user double clicks the list box row. But it is giving me error. How can I achieve this?

Code: Select all

Test.removeitem (text1.text)
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select Multiple Items to Text Box

Post by HansV »

Do you want to remove an item from the list box or text from the text box?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Select Multiple Items to Text Box

Post by adam »

I want to remove the item from the textbox.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select Multiple Items to Text Box

Post by HansV »

In that case, you should not use the RemoveItem method of the list box.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Select Multiple Items to Text Box

Post by adam »

How should I proceed. Use delete instead?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select Multiple Items to Text Box

Post by HansV »

For example:

Code: Select all

Private Sub Test_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Const sep = ", "
    Dim s As String
    Dim t As String
    Dim p As Long
    s = Me.Test.List(Me.Test.ListIndex, 0) & " " & Me.Test.List(Me.Test.ListIndex, 1)
    t = Me.txt1
    If t = "" Then
        Me.txt1 = s
    Else
        t = t & sep
        p = InStr(t, s & sep)
        If p > 0 Then
            t = Replace(t, s & sep, "")
            If Right(t, Len(sep)) = sep Then
                t = Left(t, Len(t) - Len(sep))
            End If
            Me.txt1 = t
        Else
            Me.txt1 = t & s
        End If
    End If
End Sub
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Select Multiple Items to Text Box

Post by adam »

Thanks a lot Hans. This code works great. But there's a little issue which needs to be attended.

For example, the user double clicks "Red Apple" from the list box and it gets copied to the textbox. And then when the user again double clicks "Apple" from the list box, the "Apple" from "Red Apple" gets deleted instead of "Apple" getting copied to the textbox.

How could this be avoided?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select Multiple Items to Text Box

Post by HansV »

Does your list box still have 2 columns?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Select Multiple Items to Text Box

Post by adam »

Code: Select all

Private Sub Test_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Const sep = ", "
    Dim s As String
    Dim t As String
    Dim p As Long
    s = Me.Test.List(Me.Test.ListIndex, 1)
    t = Me.txt1
    If t = "" Then
        Me.txt1 = s
    Else
        t = t & sep
        p = InStr(t, s & sep)
        If p > 0 Then
            t = Replace(t, s & sep, "")
            If Right(t, Len(sep)) = sep Then
                t = Left(t, Len(t) - Len(sep))
            End If
            Me.txt1 = t
        Else
            Me.txt1 = t & s
        End If
    End If
End Sub
Here is how I'm using the code. I have two columns. But I'm using the second column (column 1) as the column to be copied.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select Multiple Items to Text Box

Post by HansV »

Code: Select all

Private Sub Test_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Const sep = ", "
    Dim s As String
    Dim t As String
    Dim s2 As String
    Dim t2 As String
    Dim p As Long
    s = Me.Test.List(Me.Test.ListIndex, 1)
    t = Me.txt1
    If t = "" Then
        Me.txt1 = s
    Else
        s2 = sep & s & sep
        t2 = sep & t & sep
        p = InStr(t2, s2)
        If p > 0 Then
            t2 = Left(t2, p + 1 - Len(sep)) & sep & Mid(t2, p + Len(s2))
            If Left(t2, Len(sep)) = sep Then
                t2 = Mid(t2, Len(sep) + 1)
            End If
            If Right(t2, Len(sep)) = sep Then
                t2 = Left(t2, Len(t2) - Len(sep))
            End If
            Me.txt1 = t2
        Else
            Me.txt1 = t & sep & s
        End If
    End If
End Sub
Best wishes,
Hans