Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform + listbox question
Hi again ,
In an earlier mail i asked about filtering a listbox. I think i did not explain things clearly enough. Therefor i'll try again in this post: I have a little application in Excel with several sheets. One of the sheets contains client data such as name (columnA), address (columnB) , etc..... This range is called DbClients On a Userform i have a listbox which has the DBClients as rowsource What i'd like to do is to put a textbox above the listbox to filter the data in the listbox. When i type "Carl" in the textbox , the listbox should only show the records with the name "Carl" Is there a way to do this or is it to complicated. I'm using Excel 2000-2002 If not possible, please also reply. If more info needed , don't hesitate to reply. Many thanks MarMo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform + listbox question
It can be done, but two questions.
Is the name Carl the complete value in the cells, or part, and what column is the name in? What do you mean by load the listbox with the records? Is that the names or some other column? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "marmo" wrote in message ... Hi again , In an earlier mail i asked about filtering a listbox. I think i did not explain things clearly enough. Therefor i'll try again in this post: I have a little application in Excel with several sheets. One of the sheets contains client data such as name (columnA), address (columnB) , etc..... This range is called DbClients On a Userform i have a listbox which has the DBClients as rowsource What i'd like to do is to put a textbox above the listbox to filter the data in the listbox. When i type "Carl" in the textbox , the listbox should only show the records with the name "Carl" Is there a way to do this or is it to complicated. I'm using Excel 2000-2002 If not possible, please also reply. If more info needed , don't hesitate to reply. Many thanks MarMo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform + listbox question
Hi Bob ,
The name "Carl" could be a part of a value in the cells , and the name is in column B Here is an example of the cells layout: A B C D E 1 Code Name Address Zipcode Place 2 326 Carl Lewis Highstreet 23 88620 Paris 2 366 Rowan Atkin LevelStreet 6 99823 Nice 3 633 Carlisle Francs Burbonlane 5 9983 CA Amsterdam the complete range has the name DBClients (ActiveWorkbook.Names.Add Name:="DBClients", RefersToR1C1:= _ "=Sheet1!R1C1:R4C5") When the userform is loaded , the listbox is automaticly filled with the DBClients data, which is the complete range. In this example all 5 colums and all rows within the range.name DBClients are included . So if i put "Carl" in the text box , the listbox should show me "Carl Lewis" and "Carlisle Francs" Hope this anwers your suestion. If you need a real example , please let me know. Thanks in advance. MarMo "Bob Phillips" wrote in message ... It can be done, but two questions. Is the name Carl the complete value in the cells, or part, and what column is the name in? What do you mean by load the listbox with the records? Is that the names or some other column? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "marmo" wrote in message ... Hi again , In an earlier mail i asked about filtering a listbox. I think i did not explain things clearly enough. Therefor i'll try again in this post: I have a little application in Excel with several sheets. One of the sheets contains client data such as name (columnA), address (columnB) , etc..... This range is called DbClients On a Userform i have a listbox which has the DBClients as rowsource What i'd like to do is to put a textbox above the listbox to filter the data in the listbox. When i type "Carl" in the textbox , the listbox should only show the records with the name "Carl" Is there a way to do this or is it to complicated. I'm using Excel 2000-2002 If not possible, please also reply. If more info needed , don't hesitate to reply. Many thanks MarMo |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform + listbox question
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim cell As Range Dim rng As Range Dim i As Long With Me.ListBox1 .RowSource = "" .Clear For Each cell In Range("DBClients").Columns(2).Cells If InStr(cell.Value, Me.TextBox1.Value) Then .AddItem Range("DBClients").Cells(cell.Row, 1) For i = 2 To 3 .List(.ListCount - 1, i - 1) = Range("DBClients").Cells(cell.Row, i) Next i End If Next cell End With End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "marmo" wrote in message . .. Hi Bob , The name "Carl" could be a part of a value in the cells , and the name is in column B Here is an example of the cells layout: A B C D E 1 Code Name Address Zipcode Place 2 326 Carl Lewis Highstreet 23 88620 Paris 2 366 Rowan Atkin LevelStreet 6 99823 Nice 3 633 Carlisle Francs Burbonlane 5 9983 CA Amsterdam the complete range has the name DBClients (ActiveWorkbook.Names.Add Name:="DBClients", RefersToR1C1:= _ "=Sheet1!R1C1:R4C5") When the userform is loaded , the listbox is automaticly filled with the DBClients data, which is the complete range. In this example all 5 colums and all rows within the range.name DBClients are included . So if i put "Carl" in the text box , the listbox should show me "Carl Lewis" and "Carlisle Francs" Hope this anwers your suestion. If you need a real example , please let me know. Thanks in advance. MarMo "Bob Phillips" wrote in message ... It can be done, but two questions. Is the name Carl the complete value in the cells, or part, and what column is the name in? What do you mean by load the listbox with the records? Is that the names or some other column? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "marmo" wrote in message ... Hi again , In an earlier mail i asked about filtering a listbox. I think i did not explain things clearly enough. Therefor i'll try again in this post: I have a little application in Excel with several sheets. One of the sheets contains client data such as name (columnA), address (columnB) , etc..... This range is called DbClients On a Userform i have a listbox which has the DBClients as rowsource What i'd like to do is to put a textbox above the listbox to filter the data in the listbox. When i type "Carl" in the textbox , the listbox should only show the records with the name "Carl" Is there a way to do this or is it to complicated. I'm using Excel 2000-2002 If not possible, please also reply. If more info needed , don't hesitate to reply. Many thanks MarMo |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform + listbox question
Hi Bob ,
Thanks a lot for this. It works great. I only need to finetune the code to my needs. Again , thanks a lot. MarMo "Bob Phillips" wrote in message ... Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim cell As Range Dim rng As Range Dim i As Long With Me.ListBox1 .RowSource = "" .Clear For Each cell In Range("DBClients").Columns(2).Cells If InStr(cell.Value, Me.TextBox1.Value) Then .AddItem Range("DBClients").Cells(cell.Row, 1) For i = 2 To 3 .List(.ListCount - 1, i - 1) = Range("DBClients").Cells(cell.Row, i) Next i End If Next cell End With End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "marmo" wrote in message . .. Hi Bob , The name "Carl" could be a part of a value in the cells , and the name is in column B Here is an example of the cells layout: A B C D E 1 Code Name Address Zipcode Place 2 326 Carl Lewis Highstreet 23 88620 Paris 2 366 Rowan Atkin LevelStreet 6 99823 Nice 3 633 Carlisle Francs Burbonlane 5 9983 CA Amsterdam the complete range has the name DBClients (ActiveWorkbook.Names.Add Name:="DBClients", RefersToR1C1:= _ "=Sheet1!R1C1:R4C5") When the userform is loaded , the listbox is automaticly filled with the DBClients data, which is the complete range. In this example all 5 colums and all rows within the range.name DBClients are included . So if i put "Carl" in the text box , the listbox should show me "Carl Lewis" and "Carlisle Francs" Hope this anwers your suestion. If you need a real example , please let me know. Thanks in advance. MarMo "Bob Phillips" wrote in message ... It can be done, but two questions. Is the name Carl the complete value in the cells, or part, and what column is the name in? What do you mean by load the listbox with the records? Is that the names or some other column? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "marmo" wrote in message ... Hi again , In an earlier mail i asked about filtering a listbox. I think i did not explain things clearly enough. Therefor i'll try again in this post: I have a little application in Excel with several sheets. One of the sheets contains client data such as name (columnA), address (columnB) , etc..... This range is called DbClients On a Userform i have a listbox which has the DBClients as rowsource What i'd like to do is to put a textbox above the listbox to filter the data in the listbox. When i type "Carl" in the textbox , the listbox should only show the records with the name "Carl" Is there a way to do this or is it to complicated. I'm using Excel 2000-2002 If not possible, please also reply. If more info needed , don't hesitate to reply. Many thanks MarMo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox in UserForm | Excel Discussion (Misc queries) | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
UserForm Listbox in VBC | Excel Discussion (Misc queries) | |||
UserForm with ListBox | Excel Programming | |||
UserForm ListBox | Excel Programming |