display more info for listbox entry
Hi,
I have a database on a worksheet and a userform to search one column for a text string that the user puts in a textbox. The listbox shows just the filtered cells. I'd like the user to be able to select an item in the listbox and view further info for that item (ie the values of other cells in the same row). I'd put them as the caption of a label or some other "untouchable" way. For example the text search looks in the product decription (column B) and I'd like the user to see the product code (column A) and warehouse availability (Column E). -- David M |
display more info for listbox entry
Hi
You need to put some code in the Listbox click event. I'll assume your RowSource for the listbox is in A1:B3, so you have three rows of 3 columns. In the width property for the listbox I'll assume columns 2 and 3 have 0 width so you can't see them (I THINK you do this with :0:0 in the width property - check the help). Then Private Sub Listbox1_Click() Dim i as integer With Listbox1 for i = 0 to .ListCount - 1 If .Selected(i) then Userform1.LB1.Text = .List(i,1) Userform1.LB2.Text = .List(i,2) End If Exit for next i End With End Sub If you double click your ListBox in the VBE you will see this event in the drop down menu at the top right (if it is not created for you on the click) regards Paul #DIV/0 wrote: Hi, I have a database on a worksheet and a userform to search one column for a text string that the user puts in a textbox. The listbox shows just the filtered cells. I'd like the user to be able to select an item in the listbox and view further info for that item (ie the values of other cells in the same row). I'd put them as the caption of a label or some other "untouchable" way. For example the text search looks in the product decription (column B) and I'd like the user to see the product code (column A) and warehouse availability (Column E). -- David M |
display more info for listbox entry
Hi
To populate the listbox you can create an array and add it to the listbox using the .List method. your non-continuous range has to be worked around. e.g. Suppose you have three columns of data. Suppose also that these are in columns I, J and K and that the column you want to see in the listbox is column I. For the listbox set Columncount to 3 and set ColumnWidths to ;0;0. This means that the first column will take up the width of the listbox. Then Private Sub UserForm_Initialize() Dim rng As Range, cell As Range Dim ListValues(0 To 2) As Variant, ListArray() As Variant Dim ListCollection As New Collection Dim ListCount As Long With Worksheets("Test").Range("I1") 'don't need all of column I Set rng = .Range(.Cells(1, 1), .End(xlDown)) End With For Each cell In rng If InStr(1, cell, Parola.Value, vbTextCompare) Then ListValues(0) = cell.Value ListValues(1) = cell.Offset(0, 1).Value ListValues(2) = cell.Offset(0, 2).Value ListCollection.Add ListValues End If Next ListCount = ListCollection.Count ReDim ListArray(0 To ListCount - 1, 0 To 2) For i = 0 To ListCount - 1 For j = 0 To 2 ListArray(i, j) = ListCollection(i + 1)(j) Next j Next i ListBox1.List = ListArray End Sub Arrays in Listbox's must start at 0 when you count them. Items in collections must start at 1 when you count them. Hence I'm starting the loops at 0 but need an i+1 in the collection. ListCollection(i + 1)(j) refers to column j (which is 0 to 2) in ListCollection item (i+1) which can go from item 1 to item listCount. hope that helps! regards Paul #DIV/0 wrote: Hi Paul, So part of the trick is just having the data already there on the userform but hidden. Clever.... Actually the list box is a single column and I'm geting the data from a single column in the worksheet. I'll have to figure out how to set up the listbox correctly as I wasn't doing it through initializing the form. I have this sub populating it after a text search: Dim rng As Range, cell As Range With Worksheets("Offerte") Set rng = .Range("I:I") End With ListBox1.Clear For Each cell In rng If InStr(1, cell, Parola.Value, vbTextCompare) Then ListBox1.AddItem cell.Value End If Next If I initialize the listbox with more columns (hiding all but one), how can I get the results of this sub into the right column ? -- David M " wrote: Hi You need to put some code in the Listbox click event. I'll assume your RowSource for the listbox is in A1:B3, so you have three rows of 3 columns. In the width property for the listbox I'll assume columns 2 and 3 have 0 width so you can't see them (I THINK you do this with :0:0 in the width property - check the help). Then Private Sub Listbox1_Click() Dim i as integer With Listbox1 for i = 0 to .ListCount - 1 If .Selected(i) then Userform1.LB1.Text = .List(i,1) Userform1.LB2.Text = .List(i,2) End If Exit for next i End With End Sub If you double click your ListBox in the VBE you will see this event in the drop down menu at the top right (if it is not created for you on the click) regards Paul #DIV/0 wrote: Hi, I have a database on a worksheet and a userform to search one column for a text string that the user puts in a textbox. The listbox shows just the filtered cells. I'd like the user to be able to select an item in the listbox and view further info for that item (ie the values of other cells in the same row). I'd put them as the caption of a label or some other "untouchable" way. For example the text search looks in the product decription (column B) and I'd like the user to see the product code (column A) and warehouse availability (Column E). -- David M |
display more info for listbox entry
Hi
Sorry, I don't know what you are asking for in the last paragraph. Can you spell it out a bit. The columnwidths worked for me. I set it in the properties window of the listbox (the bottom left hand pane when you click on the listbox in the editor) regards Paul #DIV/0 wrote: Thanks for your help (so far) Paul, but don't think I'm going to let you get off so easily... I'm trying to get my head round this. I lost a lot of time trying to get ColumnWidths to work until I tried putting the values in quotes which solved the problem (although the help file shows it without!) You've incorporated the sub which I had on a button to be pressed after inputting the text to look for in a textbox ("Parole"), so how can I initiate the search ? -- David M " wrote: Hi To populate the listbox you can create an array and add it to the listbox using the .List method. your non-continuous range has to be worked around. e.g. Suppose you have three columns of data. Suppose also that these are in columns I, J and K and that the column you want to see in the listbox is column I. For the listbox set Columncount to 3 and set ColumnWidths to ;0;0. This means that the first column will take up the width of the listbox. Then Private Sub UserForm_Initialize() Dim rng As Range, cell As Range Dim ListValues(0 To 2) As Variant, ListArray() As Variant Dim ListCollection As New Collection Dim ListCount As Long With Worksheets("Test").Range("I1") 'don't need all of column I Set rng = .Range(.Cells(1, 1), .End(xlDown)) End With For Each cell In rng If InStr(1, cell, Parola.Value, vbTextCompare) Then ListValues(0) = cell.Value ListValues(1) = cell.Offset(0, 1).Value ListValues(2) = cell.Offset(0, 2).Value ListCollection.Add ListValues End If Next ListCount = ListCollection.Count ReDim ListArray(0 To ListCount - 1, 0 To 2) For i = 0 To ListCount - 1 For j = 0 To 2 ListArray(i, j) = ListCollection(i + 1)(j) Next j Next i ListBox1.List = ListArray End Sub Arrays in Listbox's must start at 0 when you count them. Items in collections must start at 1 when you count them. Hence I'm starting the loops at 0 but need an i+1 in the collection. ListCollection(i + 1)(j) refers to column j (which is 0 to 2) in ListCollection item (i+1) which can go from item 1 to item listCount. hope that helps! regards Paul #DIV/0 wrote: Hi Paul, So part of the trick is just having the data already there on the userform but hidden. Clever.... Actually the list box is a single column and I'm geting the data from a single column in the worksheet. I'll have to figure out how to set up the listbox correctly as I wasn't doing it through initializing the form. I have this sub populating it after a text search: Dim rng As Range, cell As Range With Worksheets("Offerte") Set rng = .Range("I:I") End With ListBox1.Clear For Each cell In rng If InStr(1, cell, Parola.Value, vbTextCompare) Then ListBox1.AddItem cell.Value End If Next If I initialize the listbox with more columns (hiding all but one), how can I get the results of this sub into the right column ? -- David M " wrote: Hi You need to put some code in the Listbox click event. I'll assume your RowSource for the listbox is in A1:B3, so you have three rows of 3 columns. In the width property for the listbox I'll assume columns 2 and 3 have 0 width so you can't see them (I THINK you do this with :0:0 in the width property - check the help). Then Private Sub Listbox1_Click() Dim i as integer With Listbox1 for i = 0 to .ListCount - 1 If .Selected(i) then Userform1.LB1.Text = .List(i,1) Userform1.LB2.Text = .List(i,2) End If Exit for next i End With End Sub If you double click your ListBox in the VBE you will see this event in the drop down menu at the top right (if it is not created for you on the click) regards Paul #DIV/0 wrote: Hi, I have a database on a worksheet and a userform to search one column for a text string that the user puts in a textbox. The listbox shows just the filtered cells. I'd like the user to be able to select an item in the listbox and view further info for that item (ie the values of other cells in the same row). I'd put them as the caption of a label or some other "untouchable" way. For example the text search looks in the product decription (column B) and I'd like the user to see the product code (column A) and warehouse availability (Column E). -- David M |
display more info for listbox entry
Hi
The way I suggested in an earlier post. Is the button you are clicking on the form with the listbox? If it is, then it is very strange to populate the listbox using a button on the same form - why not do the populating in the initialize sub as I suggested? Now use your button to run the lable changing code. regards Paul #DIV/0 wrote: Hang on. Figured it. I left ListBox1.ColumnCount = 3 ListBox1.ColumnWidths = ";0;0" in UserForm_Initialize and put all the rest in the button_Click Now I want the ListBox1_Click to put the values of the hidden columns into label captions. How do I do that ? ListIndex ? Offset ? -- David M |
display more info for listbox entry
The TextBox "Parole" is still empty when you open the form so the initialize
function will do a search for nothing and populate the listbox with the whole column and nothing changes when I type the text to find. My way the form opens with an empty textbox and an empty list. I type the word to search for and click (which is how websearches do it so is the way people expect it to work). That populates the textbox with the full text only from those cells containing the word I want. Clicking on each item should display further details (as label captions) and as I see that's what you addressed with the very first post. Duh! Duh again! for missing the ColumnWidth in the properties window. I'll work on what you've given me and will post back. -- David M " wrote: Hi The way I suggested in an earlier post. Is the button you are clicking on the form with the listbox? If it is, then it is very strange to populate the listbox using a button on the same form - why not do the populating in the initialize sub as I suggested? Now use your button to run the lable changing code. regards Paul #DIV/0 wrote: Hang on. Figured it. I left ListBox1.ColumnCount = 3 ListBox1.ColumnWidths = ";0;0" in UserForm_Initialize and put all the rest in the button_Click Now I want the ListBox1_Click to put the values of the hidden columns into label captions. How do I do that ? ListIndex ? Offset ? -- David M |
display more info for listbox entry
Hi
That makes more sense then! In the click event for the textbox you will need to run the code I put in the initialisation sub. The thing you are searching for will be Userform1.TB1.Text in your If Then Else regards Paul #DIV/0 wrote: The TextBox "Parole" is still empty when you open the form so the initialize function will do a search for nothing and populate the listbox with the whole column and nothing changes when I type the text to find. My way the form opens with an empty textbox and an empty list. I type the word to search for and click (which is how websearches do it so is the way people expect it to work). That populates the textbox with the full text only from those cells containing the word I want. Clicking on each item should display further details (as label captions) and as I see that's what you addressed with the very first post. Duh! Duh again! for missing the ColumnWidth in the properties window. I'll work on what you've given me and will post back. -- David M " wrote: Hi The way I suggested in an earlier post. Is the button you are clicking on the form with the listbox? If it is, then it is very strange to populate the listbox using a button on the same form - why not do the populating in the initialize sub as I suggested? Now use your button to run the lable changing code. regards Paul #DIV/0 wrote: Hang on. Figured it. I left ListBox1.ColumnCount = 3 ListBox1.ColumnWidths = ";0;0" in UserForm_Initialize and put all the rest in the button_Click Now I want the ListBox1_Click to put the values of the hidden columns into label captions. How do I do that ? ListIndex ? Offset ? -- David M |
display more info for listbox entry
Hi
replied privately, but I'll put the fix here to finish off the thread Private Sub Listbox1_Click() With ListBox1 UserForm1.Label1.Caption = .List(.ListIndex, 1) UserForm1.Label2.Caption = .List(.ListIndex, 2) End With Me.Repaint End Sub regards Paul #DIV/0 wrote: Thanks again, The search part is working perfectly but the ListBox_Click only works with the first item. -- David M " wrote: Hi That makes more sense then! In the click event for the textbox you will need to run the code I put in the initialisation sub. The thing you are searching for will be Userform1.TB1.Text in your If Then Else regards Paul #DIV/0 wrote: The TextBox "Parole" is still empty when you open the form so the initialize function will do a search for nothing and populate the listbox with the whole column and nothing changes when I type the text to find. My way the form opens with an empty textbox and an empty list. I type the word to search for and click (which is how websearches do it so is the way people expect it to work). That populates the textbox with the full text only from those cells containing the word I want. Clicking on each item should display further details (as label captions) and as I see that's what you addressed with the very first post. Duh! Duh again! for missing the ColumnWidth in the properties window. I'll work on what you've given me and will post back. -- David M " wrote: Hi The way I suggested in an earlier post. Is the button you are clicking on the form with the listbox? If it is, then it is very strange to populate the listbox using a button on the same form - why not do the populating in the initialize sub as I suggested? Now use your button to run the lable changing code. regards Paul #DIV/0 wrote: Hang on. Figured it. I left ListBox1.ColumnCount = 3 ListBox1.ColumnWidths = ";0;0" in UserForm_Initialize and put all the rest in the button_Click Now I want the ListBox1_Click to put the values of the hidden columns into label captions. How do I do that ? ListIndex ? Offset ? -- David M |
All times are GMT +1. The time now is 01:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com