Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox.row source
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox.row source
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 -----Original Message----- 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Listbox B if LIstbox A equals | Excel Discussion (Misc queries) | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |