listbox.row source

  • Thread starter Thread starter baha
  • Start date Start date
B

baha

hello
if the range always variable how can I make a listbox
source.
here is sample what a have.a,b,c is column 1,2,3,4,5,row
a b c
1 a1 sour bla bla
2 a2 sweet mala mala
3 a2 spicy pala pala
4 a1 sweet bla bla
5 a3 sour mala la
objects textbox1,textbox2,listbox1,commandbutton1. I want
to key in on textbox1 example"a1" is that possible for
listbox to show sour(b1),sweet(b4) and when click on b1 on
listbox textbox2 to show "bla Bla"(c1).so far i can only
make find each text1 object in the sheet and offset(0,3)
am I in wrong way?Thanks in advance have a good day.
 
userform, two textboxes ( textbox1 and textbox2) one
listbox (listbox1)
set the listbox to have 2 columns , with the boundcolumn
set to 2. leave its row source blank and the columnwidths
property to 50;0 or just ;0


on the sheet, set your table ( A1 : C5 ) to the range
name TestData.

Here's the form's code:

Option Explicit
Private rTestData As Range
Private Sub ListBox1_Click()
TextBox2 = ListBox1.Value
End Sub
Private Sub TextBox1_Change()
ListBox1.Clear
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)
Dim cell As Range
If KeyCode = 13 Then
ListBox1.Clear
For Each cell In rTestData.Columns(1).Cells
If cell.Value = TextBox1.Value Then
ListBox1.AddItem cell.Offset(0, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) =
cell.Offset(0, 2)
End If
Next
End If
End Sub
Private Sub UserForm_Initialize()
Set rTestData = ThisWorkbook.Names
("testData").RefersToRange
End Sub
Private Sub UserForm_Terminate()
Set rTestData = Nothing
End Sub



Running the form initialises the range variable so its
pointing to the range on the sheet.

When you start typing in textbox1, the listbox will
clear. Type in a1 and hit Enter. This is keycode 13. When
the keydown event traps this key, then it will populate
the listbox. It does this by comparing each cell in the
first column of the table with the entry in the text box.
A match will put the item in column B into the
list...this defaults into the first column of the list
box. The Listcount property will now return the number of
items. With this we can add the value in column C to the
second column of the listbox. The list box items are zero
based, to the index for the list is the listcount-1 and
the second column is column 1.

Clicking on an item in the listbox fires the listbox
click event and places the listbox value into textbox2.
since we set the BoundColumn as column 2 , the value
passed by the listbox is the value in the 2nd column
so if we see 'Sour', the second column will be 'S1' and
S1 is the value of this item passed to the textbox.

Patrick Molloy
Microsoft Excel MVP
 
Back
Top