none
Display Listbox items in text box RRS feed

  • Question

  • Hi,

    I have been playing around with a little userform search engine with commodities and requirements(this info is on a worksheet)

    Select a commodity from the combobox and it displays the requirements in a listbox, the reason I used a listbox, it was the only way I could work out to get the scroll bars but I have since found out a listbox doesn't wrap the text.

    I tried a label(which worked but couldn't get scroll bars), displaying in a textbox gave me an "compiler error "method or data member not found"

    Is there a way I can duplicate the list box into a text box?
    Tuesday, November 22, 2016 3:13 AM

Answers

  • Hi russkris,

    please refer the example mentioned below.

    I have following data of commodities on the sheet.

    I use the code mentioned below.

    Option Explicit
    Dim c As Range
    
    Private Sub CommandButton1_Click()
    Me.TextBox1.Text = ""
    If Me.ComboBox1.SelText = "Grains" Then
    
    For Each c In Worksheets("Sheet1").Range("A2:A7")
    
     Me.TextBox1.Value = Me.TextBox1.Value & c.Value + vbCrLf
    
    
    Next c
    ElseIf Me.ComboBox1.SelText = "Oils" Then
    For Each c In Worksheets("Sheet1").Range("B2:B7")
    
     Me.TextBox1.Value = Me.TextBox1.Value & c.Value + vbCrLf
    
    
    Next c
    Else
    For Each c In Worksheets("Sheet1").Range("C2:C7")
    
     Me.TextBox1.Value = Me.TextBox1.Value & c.Value + vbCrLf
    
    
    Next c
    End If
    End Sub
    
    Private Sub UserForm_Click()
    
    End Sub
    
    Private Sub UserForm_Initialize()
    Me.ComboBox1.AddItem ("Grains")
    Me.ComboBox1.AddItem ("Oils")
    Me.ComboBox1.AddItem ("Metals")
    End Sub
    

    when you select the commodity from the combobox and click the button. data will be display in the textbox.

    Output will be like mentioned below.

    you can see the scroll bar to view the list of data and text will be wrapped if it is long.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 23, 2016 4:13 AM
    Moderator

All replies

  • Hi,

    Can you please share an example like how long or in how many numbers you requirements will be displaying on selecting a single value in Combobox.

    Textbox can be used for this purpose easily as it has got all options like enabling Horizontal and vertical scroll bar, wrapping, multiline etc.

    If you share some sample, I can share the VBA code for you.


    Vish Mishra

    Tuesday, November 22, 2016 9:42 PM
  • Hi russkris,

    please refer the example mentioned below.

    I have following data of commodities on the sheet.

    I use the code mentioned below.

    Option Explicit
    Dim c As Range
    
    Private Sub CommandButton1_Click()
    Me.TextBox1.Text = ""
    If Me.ComboBox1.SelText = "Grains" Then
    
    For Each c In Worksheets("Sheet1").Range("A2:A7")
    
     Me.TextBox1.Value = Me.TextBox1.Value & c.Value + vbCrLf
    
    
    Next c
    ElseIf Me.ComboBox1.SelText = "Oils" Then
    For Each c In Worksheets("Sheet1").Range("B2:B7")
    
     Me.TextBox1.Value = Me.TextBox1.Value & c.Value + vbCrLf
    
    
    Next c
    Else
    For Each c In Worksheets("Sheet1").Range("C2:C7")
    
     Me.TextBox1.Value = Me.TextBox1.Value & c.Value + vbCrLf
    
    
    Next c
    End If
    End Sub
    
    Private Sub UserForm_Click()
    
    End Sub
    
    Private Sub UserForm_Initialize()
    Me.ComboBox1.AddItem ("Grains")
    Me.ComboBox1.AddItem ("Oils")
    Me.ComboBox1.AddItem ("Metals")
    End Sub
    

    when you select the commodity from the combobox and click the button. data will be display in the textbox.

    Output will be like mentioned below.

    you can see the scroll bar to view the list of data and text will be wrapped if it is long.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 23, 2016 4:13 AM
    Moderator
  • @Vish Mishra - Thank you for your reply... I was more looking to see if you could actually do it..

    @Deepak - Thank you very much... I am a noob with vba and your code worked great,

    Wednesday, November 23, 2016 6:49 PM