Return Item description in a Userform

[joand]

In Sheet1 I have 2 columns of data (Column A and Column B), where A is the item name and B is the item description. The data in column A is loaded into a userform listbox. How do I return into a userform textbox the corresponding item description (Column B) of the selected item in the listbox?
I'm still learning userforms

 

[bertie]

Consider this data set:


<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; ">Name</td><td style="font-weight:bold; ">Desc</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Item1</td><td >Desc1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Item2</td><td >Desc2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Item3</td><td >Desc3</td></tr></table>


You would populate the ListBox in the UserForm_Initiallize event:

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
    lstItemName.List = Sheets("Sheet1").Range("A2:A4").Value
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

ListBoxes have a ListIndex property.
The List starts at base zero (+1) and our data set has a header row (+1).
So adding two onto the ListIndex would return the row number of the data we want returned.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdProcess_Click()
    txtItemDesc = _
        Sheets("Sheet1").Range("B" & [COLOR="Red"]lstItemName.ListIndex + 2[/COLOR]).Value
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Akashwani]

Hi,

I don't write or understand VBA code, so I cannot help you with coding, sorry.

Take a look here....
http://www.contextures.com/xlUserForm02.html

I use the following code to load 2 Text boxes based on the value selected in a ComboBox List....

Private Sub ComboBox1_Change()

Dim Part As Range
Dim X As Long

''You will need to change the range to suit your needs.
    Set Part = Sheets("Sheet2").Range("A2:A10").Find(ComboBox1, lookat:=xlWhole, LookIn:=xlValues)
 
''2 is the first text box (TextBox) to be populated with data,3 is the last, change 2 & 3 to suit
       For X = 2 To 3

            Me.Controls("TextBox" & X) = Part.Offset(0, X - 1)
        
        Next
      
End Sub

I hope this helps.

Good luck.

Ak

 

[joand]

Thanks a lot guys!