Announcement

Collapse
No announcement yet.

Transfer from ListBox to TextBox

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Transfer from ListBox to TextBox



    Hi All,

    I have a parent excel form, on which I have a button which opens another form with two list boxes.
    From the first listbox, I select specific fields and move them to the second listbox.
    When I click the submit button on the second listbox, I need these fields to be loaded into
    the textbox,which is on the parent form, with comma separated. Along with this fields and other filters, I will
    eventually run a report.
    There could be more than 25-30 fields selected.

    Can someone point out the direction, I can proceed?

    Thanks,

    pragov

  • #2
    Re: Transfer from ListBox to TextBox

    You'll need to loop through the list box and retrive the values from the one form, and transfer to the other.
    http://stackoverflow.com/questions/9...sheet-in-excel
    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

    Comment


    • #3
      Re: Transfer from ListBox to TextBox

      Hi ,

      If I need to put the above code inside a command button click procedure, how would I do that?

      Currently I did it like this and it gave an error
      Code:
      Private Sub cmdSubmit_Click()
       If lstBox2.Selected(lstBox2.ListIndex) Then
              If StrSelection = "" Then
                  StrSelection = lstBox2.List(lstBox2.ListIndex)
              Else
                  StrSelection = StrSelection & "," & lstBox2.List(lstBox2.ListIndex)
              End If
          Else
              StrSelection = Replace(StrSelection, "," & lstBox2.List(lstBox2.ListIndex), "")
          End If
      End Sub

      Comment


      • #4
        Re: Transfer from ListBox to TextBox

        IT sounded like you already had the code to transfer items from listbox 1 to listbox 2. If that's true, your command button needs to loop over the lstBox2.List and retrive every value.
        Best Regards,
        Luke M
        =======
        "A little knowledge is a dangerous thing."

        Comment


        • #5
          Re: Transfer from ListBox to TextBox

          Yes, I have them moved to lstBox2 from lstBox1.
          How do I loop over the lstBox2.List and retrive every value?

          Comment


          • #6
            Re: Transfer from ListBox to TextBox

            Example of a loop:
            Code:
            Dim lItem As Long
            Dim allItems As String
            
            
            
            
            For lItem = 0 To ListBox2.ListCount - 1
                allItems = allItems & ListBox2.List(lItem)
            Next lItem
            MsgBox allItems
            Best Regards,
            Luke M
            =======
            "A little knowledge is a dangerous thing."

            Comment


            • #7
              Re: Transfer from ListBox to TextBox

              On my parent form, frmReport I have this textbox,txtboxFields. If I run your code on the Child form's submit button,then how does it get transferred to the txtbox of the Parent form?
              Code:
              Private Sub cmdSubmit_Click()
              Dim lItem As Long
              Dim allItems As String
                
              For lItem = 0 To lstBox2.ListCount - 1
                  allItems = allItems & lstBox2.List(lItem)
              Next lItem
              
              End Sub

              Comment


              • #8
                Re: Transfer from ListBox to TextBox

                As I don't have your file, I'm not sure of the exact architecture, but presumable you would do something like:
                Code:
                UserForm2.TextBox1.Value = allItems
                Make sure you are hiding your forms, and not unloading them (until your code is done), so that you can still access them.
                Best Regards,
                Luke M
                =======
                "A little knowledge is a dangerous thing."

                Comment


                • #9
                  Re: Transfer from ListBox to TextBox

                  Thanks a lot.Appreciate your help.

                  Comment


                  • #10
                    Re: Transfer from ListBox to TextBox

                    When I do the above way,
                    Code:
                    Private Sub cmdSubmit_Click()
                    Dim lItem As Long
                    Dim allItems As String
                      
                    For lItem = 0 To lstBox2.ListCount - 1
                       'allItems = allItems & lstBox2.List(lItem)
                        allItems = allItems & "," & lstBox2.List(lItem)
                    Next lItem
                    
                    AdhocQuery.txtboxFields.Value = allItems
                    Me.Hide
                    End Sub
                    I am getting the result in the textbox as
                    ,CREATION TS,Global CallID CallManagerId,nodeId,PROCESS TS.

                    why is the comma appearing before the first field?

                    Comment


                    • #11
                      Re: Transfer from ListBox to TextBox

                      If you actually look at and try to understand, the code, you'll see why. Move the comma to the end. Of course you'll then have the problem of a trailing comma.

                      This is a simple exercise in logic - nothing to do with programming, at all.

                      Comment


                      • #12


                        May I ask a follow up question? If this needs to be a new question completely, please feel free to move it, and reference the OP. Thank you!

                        I modified the example code from LukeM (Excel Ninja), to achieve:
                        • pulling Selected Items only from 4 separate list boxes, then
                        • placing the values into a textbox, with punctuation like parenthesis between certain strings.
                        • clearing all listbox selections, in preparation for additional rounds of selection
                          See my humble code below. (leaving out my .AddItem lists for sake of shorter entry)
                        EDIT: I found my answer to the question I asked (in the quote below)!
                        It was so simple in the end, i am almost embarrassed I posted the question.
                        However, i will add my solution directly below the quote, incase anyone else was looking for similar.

                        If anyone has suggestions for cleaning the code up or sees possible weak points, please point them out. So far I am sure it's only the equivalent of duct taped bits and pieces.


                        What I would like to figure out next is how to add new string combinations to the end of the textbox value, without erasing the first entries.

                        For example, imagine a user:
                        • chooses an option from each listbox, then
                        • clicks the ADD button, then
                        • goes back to each list box to select a new combination of answers, then
                        • clicks ADD again to amend the textbox value with a NEW collection of strings added to the end of the textbox.

                        I will keep experimenting in the meantime and post if I find a solution. I love solving this stuff on my own, but occasionally my brain is just fried and I need help from ninjas like you all!
                        SOLUTION:
                        I simply added two new DIMs, then defined those new strings before I set the textbox value.
                        Code:
                         Dim Collection As String 'All strings as a collection
                            Dim Current As String
                           '.....skipping all the code below from my OP
                        
                            Collection = SelectedItems001 & SelectedItems002 & "(" & SelectedItems003 & SelectedItems004 & ")"
                            Current = txtbx_PreviewChoices.Value
                            txtbx_PreviewChoices.Value = Current & Collection


                        My humble code from Original Post:
                        Code:
                         
                        Dim i As Integer
                        
                        Private Sub CommandButton1_Click() 'ADD Selections
                        
                        Dim SelectedItems001 As String 'All Selected listbox_Actions Items
                        Dim SelectedItems002 As String 'All Selected listbox_CustInfo Items
                        Dim SelectedItems003 As String 'All Selected listbox_ImgsRcvd Items
                        Dim SelectedItems004 As String 'All Selected listbox_SORs Items
                        
                        For i = 0 To listbox_Actions.ListCount - 1 'Count List Items of listbox_Actions
                        If listbox_Actions.Selected(i) = True _
                        Then SelectedItems001 = SelectedItems001 & listbox_Actions.List(i)
                        'If item selected, then add to String
                        Next i
                        
                        For i = 0 To listbox_CustInfo.ListCount - 1 'Count List Items of listbox_CustInfo
                        If listbox_CustInfo.Selected(i) = True _
                        Then SelectedItems002 = SelectedItems002 & listbox_CustInfo.List(i)
                        'If item selected, then add to String
                        Next i
                        
                        For i = 0 To listbox_ImgsRcvd.ListCount - 1 'Count List Items of listbox_ImgsRcvd
                        If listbox_ImgsRcvd.Selected(i) = True _
                        Then SelectedItems003 = SelectedItems003 & listbox_ImgsRcvd.List(i)
                        'If item selected, then add to String
                        Next i
                        
                        For i = 0 To listbox_SORs.ListCount - 1 'Count List Items of listbox_SORs
                        If listbox_SORs.Selected(i) = True _
                        Then SelectedItems004 = SelectedItems004 & listbox_SORs.List(i)
                        'If item selected, then add to String
                        Next i
                        
                        'Set Textbox value as String "SelectedItems###" with paranthesis around ImgsRcvd & SORs
                        txtbx_PreviewChoices.Value = _
                        SelectedItems001 & SelectedItems002 & "(" & SelectedItems003 & SelectedItems004 & ")"
                        
                        
                        'This section points to subs like the example sub click event below (In my full code, there is one for each chkBox listed)
                        chkBox_Actions.Value = True
                        chkBox_CustInfo.Value = True
                        chkBox_ImgsRcvd.Value = True
                        chkBox_SORs.Value = True
                        
                        chkBox_Actions.Value = False
                        chkBox_CustInfo.Value = False
                        chkBox_ImgsRcvd.Value = False
                        chkBox_SORs.Value = False
                        
                        End Sub
                        
                        '------------------------------------------------------------------------------
                        
                        Private Sub chkBox_Actions_Click() 'Check/Uncheck All
                        
                        If chkBox_Actions.Value = True Then
                        For i = 0 To listbox_Actions.ListCount - 1
                        listbox_Actions.Selected(i) = True
                        Next i
                        End If
                        
                        If chkBox_Actions.Value = False Then
                        For i = 0 To listbox_Actions.ListCount - 1
                        listbox_Actions.Selected(i) = False
                        Next i
                        End If
                        End Sub
                        Sincerely,
                        Henri
                        Last edited by Sir_Henri_O; 1 week ago.

                        Comment

                        Working...
                        X