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