none
Populate Textboxes based on Listbox selected Item

    Question

  • hi,

    i need to get the row of the listbox selected item then populate my textbox with a value from column D.

    EXAMPLE:

    LISTBOX ITEMS

    car

    box

    ring

     

    textbox: 23

    this is how it is in the workshet

    col1       col2    col3   col4

    car        blue    suv    23

     

    so when i select CAR (the item) in my listbox then i want to display 23 (the price) in my textbox.

     

    thanks

    Friday, June 17, 2011 10:17 AM

Answers

  • Hi

    I think the code your looking for is

    Private Sub ListBox1_AfterUpdate()
    Me.TextBox1.Value = Application.WorksheetFunction.VLOOKUP(ListBox1.value,Range("A1:D20"),4,FALSE)

    End Sub

     

    Where range A1:D20 conatins your data.  Obviously you will need to edit this to match your circumstances.

    Hope this helps

     


    G North MMI
    Friday, June 17, 2011 4:15 PM

All replies

  • Is the ListBox and TextBox on a Userform? If so, then in properties set the listbox columncount to 4.

    Then set the widths of the columns. Start with the first column about 20 and the remaining columns 0 so they do not display. You will have to play with the first column width to get the required width but don't make it too small to start with because you may not see anything at all. To set the columns widths just put them in as 20,0,0,0 and xl will then change them to correct format.

    Listbox Rowsource will be the range including the 4 columns like this: Sheet1!A2:D6

    In the AfterUpdate event for the ListBox insert the code below. Note that the columns count starts from zero so 4th column is addressed as column 3.

    Private Sub ListBox1_AfterUpdate()
      Me.TextBox1.Value = Me.ListBox1.Column(3)
    End Sub

    If the ListBox and TextBox are on the worksheet so long as you have used ActiveX controls then nearly the same except instead of property Rowsource it is called ListFillRange.


    Regards, OssieMac
    Friday, June 17, 2011 11:10 AM
  • hi OssieMac

    i dont think you are understanding me clearly. the listbox has only one column. the column displays all values from column c in the activeworksheet.

    so based on the selected item from the listbox, my textbox should show the sorresponding price to that selected item. The corresponding price should be the value in column D.

    Friday, June 17, 2011 11:59 AM
  • Hi

    I think the code your looking for is

    Private Sub ListBox1_AfterUpdate()
    Me.TextBox1.Value = Application.WorksheetFunction.VLOOKUP(ListBox1.value,Range("A1:D20"),4,FALSE)

    End Sub

     

    Where range A1:D20 conatins your data.  Obviously you will need to edit this to match your circumstances.

    Hope this helps

     


    G North MMI
    Friday, June 17, 2011 4:15 PM
  • Hi,

        I went through this code & it seems to be very useful. But I have a question if a userform has 4 different textbox and when the user select a item in listbox all the 4 textbox should show values in list box like

    TextBox1= Listbox. column (1) Value

    TextBox2= Listbox. column (2) Value

    TextBox3= Listbox. column (3) Value

    TextBox4= Listbox. column (4) Value

    Can this be possible

    Saturday, August 02, 2014 7:11 AM
  • Hello,

          I was able to sort out the same by following code:

    Private Sub ListBox1_AfterUpdate()
       Me.TextBox1.Value = Me.ListBox1.Column(0)
        Me.TextBox2.Value = Me.ListBox1.Column(1)
         Me.TextBox3.Value = Me.ListBox1.Column(2)
     End Sub

    But the question is if the user want to modify the value of Column 2 through Textbox3. How this can be achieve

    Saturday, August 02, 2014 7:15 AM