×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Concatenate two listboxes in a textbox

Concatenate two listboxes in a textbox

Concatenate two listboxes in a textbox

(OP)
Just wondering if someone can help with the macro to Concatenate two listboxes in a textbox.  I have tried but I can't figure it out.

I have two listboxes filled with selections from BTC sheet, these listboxes are on a userform. The two are exactly alike since it is designed for rev changes.

I have a textbox that already is running if you type old and new rev, but want to make it so click, click and your done instead of typing

Your help is most appreciated

RE: Concatenate two listboxes in a textbox

You'll need to provide more details.

I'll guess that each of the list boxes has a list of items in it, and you want to combine one from list 1 and one from list 2 into a text box?

If so, something like:

CODE

Private Sub ListBox1_DblClick(ByVal cancel As MSForms.ReturnBoolean)
TextBox1.Value = ListBox1.Value
End Sub

Private Sub ListBox2_DblClick(ByVal cancel As MSForms.ReturnBoolean)
TextBox1.Value = TextBox1.Value & " " & ListBox2.Value
End Sub

RE: Concatenate two listboxes in a textbox

use the following code snippet in the userform code pane

CODE

Private Sub ListBox1_Click()
TextBox1.Text = "" & ListBox1.Text
End Sub

Private Sub ListBox2_Click()
With TextBox1
.Text = .Text & ListBox2.Text
End With
End Sub
Of course this assumes ...

you've called your listboxes
ListBox1 & ListBox2

you've called your textbox
Textbox1

RE: Concatenate two listboxes in a textbox

(OP)
Ok, to start,,, I double click on a row in which I want information stored....and the Userform comes up. Within the userform... the two listboxes are exactly alike.. have A, B, C, D, E, F in each one,  designed for Rev change.  

So what I am trying to achieve is the user clicks on the selection in LB1 and one in LB2 so the textbox shows the choices Concatenated. e.g. A / E.  I already have the textbox coded to the placement on the sheet. If you need to see the lengthy code I can post it.

Thanks for the help

RE: Concatenate two listboxes in a textbox

(OP)
Here is the code anyhow.  I noticed that it is not working exactly as stated because of the code below.... I just need to see what I have to tweek for it to work...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim UForm As UserForm33
    Dim Sel_Row As Long
    Dim Cont_Item As MSForms.Control
    Dim Col_Name As String
    Dim ListBox_Item As MSForms.ListBox
    Dim Cnt1 As Integer
    Dim Cnt2 As Integer
    Dim Flag
  

    Cancel = True

    Set UForm = New UserForm33

    Sel_Row = Target.Row
    
    'POPULATE THE USERFORM FIELDS
        
    'TEXT BOXES
    UForm.TextBox4.Value = Me.Range("A" & Sel_Row).Value
    UForm.TextBox2.Value = Me.Range("B" & Sel_Row).Value
    UForm.TextBox5.Value = Me.Range("F" & Sel_Row).Value
    UForm.TextBox6.Value = Me.Range("G" & Sel_Row).Value
    UForm.TextBox7.Value = Me.Range("N" & Sel_Row).Value
    UForm.TextBox3.Value = Me.Range("C" & Sel_Row).Value
    UForm.TextBox10.Value = Me.Range("H" & Sel_Row).Value
    
   '    OPTION BUTTONS



  
 
'POPULATE LIST BOXES
    
    UForm.ListBox1.List = Sheet4.Range("E2:E11").Value
    UForm.ListBox4.List = Sheet4.Range("C2:C5").Value
    UForm.ListBox5.List = Sheet4.Range("G2:G8").Value
    UForm.ListBox6.List = Sheet4.Range("M2:M4").Value
    UForm.ListBox7.List = Sheet4.Range("O2:O8").Value
    UForm.ListBox8.List = Sheet4.Range("P2:P8").Value
    
'SELECT IN LISTS
    
    For Each Cont_Item In UForm.Controls
    
        If TypeName(Cont_Item) = "ListBox" Then
            
            Set ListBox_Item = Cont_Item
            
            Col_Name = ""
            
            Select Case Cont_Item.Name
            
                Case UForm.ListBox1.Name
                    Col_Name = "O"
                Case UForm.ListBox4.Name
                    Col_Name = "E"
                Case UForm.ListBox5.Name
                    Col_Name = "D"
                    
            End Select
            
            
        End If
            
    Next Cont_Item
    
    Manual_Quit = False
    UForm.Show
    
    If Manual_Quit Then
        
        Exit Sub
        
    End If
    
    
'POPULATE FROM USERFORM
    
    
'TEXT BOXES
    Me.Range("A" & Sel_Row).Value = UForm.TextBox4.Value
    Me.Range("B" & Sel_Row).Value = UForm.TextBox2.Value
    Me.Range("F" & Sel_Row).Value = UForm.TextBox5.Value
    Me.Range("G" & Sel_Row).Value = UForm.TextBox6.Value
    Me.Range("N" & Sel_Row).Value = UForm.TextBox7.Value
    Me.Range("C" & Sel_Row).Value = UForm.TextBox3.Value
    Me.Range("H" & Sel_Row).Value = UForm.TextBox10.Value
    
'OPTION BUTTONS
   
     
    
'LIST BOXES
    
    For Each Cont_Item In UForm.Controls
    
        If TypeName(Cont_Item) = "ListBox" Then
            
            Set ListBox_Item = Cont_Item
            
            Col_Name = ""
            
            Select Case Cont_Item.Name
            
                 Case UForm.ListBox1.Name
                    Col_Name = "O"
                Case UForm.ListBox4.Name
                    Col_Name = "E"
                Case UForm.ListBox5.Name
                    Col_Name = "D"
                Case UForm.ListBox6.Name
                    Col_Name = "L"
                Case UForm.ListBox7.Name
                    Col_Name = "H"
                Case UForm.ListBox8.Name
                    Col_Name = "H"
    
                    
            End Select
            
' Deleting the 9 rows below,,, will not give me an error... however, List boxes are not being entered into the cells
            
            For Cnt2 = 0 To ListBox_Item.ListCount - 1

                If ListBox_Item.Selected(Cnt2) = True Then

                    Me.Range(Col_Name & Sel_Row).Value = ListBox_Item.List(Cnt2)

                    Exit For

                End If

            Next Cnt2

            Set ListBox_Item = Nothing
            
        End If
            
    Next Cont_Item
    
    
    Set UForm = Nothing


End Sub

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources