Display Search Results in a ListBox - Excel VBA
ฝัง
- เผยแพร่เมื่อ 14 ต.ค. 2024
- In this video, we display search results in a listbox on a userform in Excel VBA.
In this scenario, the user is searching for a product. They enter a keyword and the search results are shown in a listbox. They then make a selection and it is added to the spreadsheet.
Enrol in the online Excel VBA course for beginners - bit.ly/2JvnnRv
Download the file and code here - www.computerga...
During the video, we create a dynamic named range using the technique explained in this video - • Create a Dynamic Range... - dynamic named range
** Online Excel Courses **
Excel VBA for Beginners ► bit.ly/2JvnnRv
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2t3netw
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2viGg3J
Find more great free tutorials at;
www.computerga...
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
![ILLSLICK - WINTER IS COMING [Official Video]](http://i.ytimg.com/vi/dwTMeM1zIhQ/mqdefault.jpg)
nobody made such a search system in excel which is so effective like this one
Merhaba vermiş olduğunuz bilgiler için çok teşekkür ederim
Thanks Computergaga!
It helped me a lot. I am new to customise excel workbook via VBA. It is easy to make many changes in code to suit my requirements as I know VB coding. Your logic to copy search results to new range, did the trick. I was attempting autofilter which was not giving me visual output I needed.
Once again I thank you.
You're welcome, Sushil. Thank you.
Excellent vid, just the right level of info and I was so pleased when you capitalised the 'p' because it was bugging me also.
Great to hear! Thank you 👍
@@Computergaga I'm rather new to VBA, and very much self taught (with a fair amount of Googling), currently trying to create a stock control system for laboratory traceability purposes and the first half of your video showed me just how to create a search function to go backwards from a stock lot number to the specimen number, and you've made it so simple even a novice like myself can follow, I really appreciate that even though you said you were keeping it short you took the time to show what related to what and were data was being pulled from. I've just subscribed because I have no doubt I'll find more useful info on your channel and I want to be able to find it easily. Thanks so much for taking the time to make these.
Great Video.... this works great. Is there a way to select multiple products (ie. if you searched on Louisiana and it returned a list of 8 products containing Louisiana), how could I select 2 or more of the products without having to do a second or third search? Example: I'm searching on a golf course that has multiple tees (Men's Black, Blue, White, Red, and Ladies White and Red tees), and I wanted to return the details of Men's White and the Ladies Red tees. Is there a suggested way to pull both desired tees without having to go through the process of describing a second search?
Very useful. Thank you. What do I need to do if I want same search user form, with same stock data and same product search, but for two form sheets, lets say sheet "form" and sheet "form1"?
Thanks for the video very useful
If I could ask you , how did you use the vlookup function in the first minute of the video how do you type in the cell and you take the row from the other sheed.. Do you have a video about this ?
Thanks..... I have been looking for this type of solution for two days.....
I implemented this inside a userform and all populated a google search like suggestions in combobox drop down....😀😀
Excellent! Happy to help, Harold.
Great how-to! Thanks! I need a little help though. If I want to use this on another sheet and DON'T want the Worksheets("Stock Data").Activate to actually show, but stay in the sheet I work on. Then how do I do that? The cmdSearch_Click() should just run in the background.
Hi! thank you very much for this tutorial. What will be the instr code for a search on multiple columns and not just one?
Louisiana Fiery Pepper Sauce. Well, ok!!! 🔥🔥🔥
Good work on this project!
Thanks Oz.
Thanks Computergaga!
One Question:
In this video you can only search matches in column B "Product name".
How can I make it search in both columns A "ID" and B at the same time?
Please make necessary changes in If condition to search in as many columns.
If InStr(1, .Cells(RowNum, 4).Value, tbSearchText.Value, vbTextCompare) > 0 Or _
InStr(1, .Cells(RowNum, 5).Value, tbSearchText.Value, vbTextCompare) > 0 Or _
InStr(1, .Cells(RowNum, 6).Value, tbSearchText.Value, vbTextCompare) > 0 Then
This code is from my project which I changed to suit basic requirement and you are asking for same.
@@sushilpugalia2831 This is similar to what I was looking for in a comment I posted above. The Or_ creates a compile error though so I'm not sure how this is working for you
Thanks for this video.
I kindly need help here, if there are 3 worksheets available, how do you then search through these worksheets and display the result in the list-box?
sir do you have a simple inventory related to this video?
Curious, can you use any wild card search’s in the text search box?
Allan, this is a great video!
Thank you Sandeep.
Thank u for this very useful tutorial...
You're welcome. Thank you Pembelajar.
This is superb, this has worked really well. I wonder, instead of adding the results of the listbox onto a spreadsheet could you select the search results you want then add them into another userform. For example one of the columns on the listbox results could be date, which when added would go into a userform - textboxdate..... if so, I would love to know how to do that!!
Thanks for sharing thin interesting video.
Thank you Syed.
hi i tried using this code for my project, so mainly i used your codes and i changed the name of the sheets to suit my project. my problem is on the product search sheet is where the search results is going to appear, so if i search something multiple results will show based on the keyword, instead of populating one row at a time, my product search sheet populates all the search results, i mean all the suggestion that appears in the list box, goes into the product search sheet, do guide me on how to fix this, thank you so much.
This doesn't work on mac, because row source is not supported, so nothing comes back in the listbox. Any suggestion or change to the listbox code so that it can work even on mac? This is perfect for my needs, but unfortunatly works perfectly only on windows computer. Thanks for the help
Hi. Thanks for the video! Helped me fixing script a lot :) Question: If in data inventory list there is a empty cells exist, how to make it continue scanning rows without stopping?
Hi , every product has an Id , counta function count only cells with something in there .You could use Range("A2").CurrentRegion.Rows to count rows involved in range you need to.
Thanks a lot! this is very useful video..
Thank you, Rajesh.
Thanks for the code, it seems the video, your example was 2 or 3 data can load fast on listbox, as I tried this, I am trying to load multiple rows of data like above 100 rows, I encounter Not Responding first on Excel, although it is back in a while like 15sec. Is there a way for this, to load hundreds of column based on criteria?
Try this for an alternative in google sheets
th-cam.com/video/XOzRCwyCkdA/w-d-xo.html
Thanks for this amazing video. Although I encountered a problem if there are no results when searching. The message box pops up which is its purpose but then since there is no list to display, I get an Error code 380. "Could not set the RowSource property. Invalid property value." Pls help.
This might be a solution, you can assign the property like: Listbox1.RowSource = Empty inside that If statement and the list will be just blank. I know it might be late but i hope this helps someone
So if I was doing something similar but copying the data to a userform I would replace row 10 with "Userform2.Textbox1.value = Worksheets("Product Search").cells(listboxrow,1).value" ?
thanks for your video demonstration.its really great.
I used the same for my application. but, could you please help me, how i can make this to filter the listbox values to a combo box search value. presently its shows all the values in listbox.
I did mine to use a drop down list instead of search box, set up a drop list as normal then name it txtKeywords in its properties then it will work to the code in this video, txtKeywords in the code is reference to the text search to be done.
Hi Computergaga. In this video, you mentioned about worksheet construct, but you didn't show example because it was only three (3) columns. I have a work sheet with over 50 columns, therefore i would like you to please send me the "construct" method, so that i don't have to type so many data for my many columns worksheet. Thank you in advance.
hi sir i have a question about my project about searching the textbox it is possible to search the last 3 digit number using command button and textbox? thank you
Hi Alan, great tutorial!
I have a question about the InStr function you used.
Is there a difference between the InStr function and Like. I think the result is the same.
Kind regards
Willem
Yes, the Like operator will work just aswell. They are very similar. You can use wildcard characters with Like. And with Instr you can specify whether to match case or not.
Sir, kindly explain and make a video about 'Error in loading DLL' in Microsoft Visual Basic.
Thanks for the good sequence on how create data entry with search function
But I need to know what is difference between AddData and Update Cimmandbutton
Difference is: when you Add something , you put some data in area , when you update you modify an existing data in that range.
thank you soooooo much I'm officially subscribed !!!!!
Excellent! Happy to help Zineb.
thank you for this video it's what i was hoping to find , i tried it but somehow it show me this code If InStr(1, Cells(RowNum, 2).Value, txtkeywords.Value) > 0 Then
highlighted in yellow ? what is the problem plz ( ps i tried everything like in the video )
txtkeywords
Thank you for this vidéo. it would have been a "cooler" version if the user doesn't have to click on a search button . A google-search like search bar with results showing in the the listbox.
مرسی
خوش آمدید
I did this and it works! but unfortunately it does not show specific data, what I mean is there are other data in the list box. how can I fix it?
Hello, I've got a question what about having a list but the search criteria could be any of the column, lets say column 1 is part number, column 2 is common code, column 3 is description, how can I create such offset with that in mind, thank you.
you can use AND operator in searching for keyword. Starts in 14:51 in the video. You have to change this part (rownum,2) and the txtkeywords.value.
@@shielaquimbo1355 where exactly will the AND function go....
Is it possible to have the order form accept free form text/data?
Is there a way to make this search for the text in more than one column ?
Sure. I don't see why not.
@@Computergaga What changes would need to be made to the VBA. As it is the column number is static and I havent been able to figure out how to make it search all rows & columns
hi, i have required vba code in excel who manuplate data in listbox more than 10 columns, can u help plz
I wonder you could solve this problem for me . here we are ... I created a data entry form and linked the database below so that user can select a particular row and then edit or delete so far so good The issue is I want to make this list to be in descending order and as and when a new row of data is entered this appear right at the top so that the user know what is the last piece of data entered
Good video..
Thank you, Martin.
Is there a code to filter data in a listbox from a textbox with more than 1 million rows?
Plzzz reply This vba code is not running on other computer .but only running in computer where it is made .
Are your macros enabled on other computer?
HI IS THERE ANYWAY WE COULD DISPLAY ALWAYS, ONLY LAST ROW OF DATA ENTERED TO APPEAR IN THE FORM
Hi. I have tried to replicate this search function (don't require the Add Product cmd). But when I'm doing a search, then another search straight after, the listbox seems to populate with both the old and new search results. Is there anyway to resolve this? Thanks
Worksheets("SearchCus").Range("A2:I100").ClearContents
I got the debug on "if Instr(1,Cells(Rownum, 2) and so on. Why??
how to clear the listbox as well as the product search range/tab you created?
Either add the code you have in initialisation
Worksheets("product search").range("A2:c2").clearcontents
Or just
Me.lstsearchresults.listindex = -1
Enter these before you run the do until loop
Hi, need help with a proyect, I did the exact same steps as the video but when I try to run the UserForm and search something I get the Run-time error "380" RowSource property invalid value, the referenced Dynamic range has the exacte same name as the one I coded in VBA, also tried coding the name inside brackets but didn´t worked... hope you can help me fix this issue, greetings.
Hi Diego Graciano,
if you still Need a solution for this Problem, try to Change the SearchResults to your Workfolder and not a single worksheet, then it´ll work.
@@sybersepp What do you mean by that? not sure I understand thanks
I have made full file like this, now when I am searching some entry multiple items are coming in list box and when I select some entry to add, all that listbox searched entry is displayed in excel sheet, I want only selected entry,,,, what to do
The technique I use is the one covered in the video. I would like to help more, but I have a demonstration there.
Thanx bro!
My pleasure Leandro.
Thanks, I followed your video from A to Z but I still got the same problem, when I type in ID I will got a message " No product were found that match your search criteria" and when I type in Name of product I will got this text,"Run-time error `380 Could not set the RowSource property, Invalid property value." and when I click "Debug" it will mark lstSearchResults.RowSource = "SearchResults" with yellow background. Pls Help
It is hard to know the root of the problem exactly Emmy, but it certainly is not happy with the search results. Either they are not working correctly to build up the named range. Or it could be a mis-type in the named range.
The yellow highlight is Excel indicating the problem occurs at that statement. There could be a few reasons why it cannot assign SearchResults to the RowSource though.
Hi emmy onu,
if you still Need a solution for this Problem, try to Change SearchResults to your Workfolder and not a single worksheet, then it´ll work. you have to Change it in Name-Manager
where can the vlookup tutorial be found?
support.office.com/en-us/article/video-vlookup-when-and-how-to-use-it-9a86157a-5542-4148-a536-724823014785
💯💯
😊
Run-time error `380`` Could not set the RowSource property. Invalid property value, Help Pls!
Check the named range is assigned to the rowsource like in the video. Wrapped inside double quotations and spelt correctly.
If not this, have a look at the named range itself. Is the formula in there accurate. Is that named correctly and not mis-typed.
⭐️⭐️
😊
This a formula that I type in "Refer to" field.
=offset('Item Search'!$A$2,0,0, counta('Item Search'!$A:$A)-1,5)
Question:
When I try to close windows, this error appears:
-------------------------------------------------
There's a problem with this formula.
Not trying to type a formula?
Whene the first character is an equal (=) or minus (-) sign,
Excel thinks it's a formula:
.You type =1+1, cell shows:2
To get around htis, type an apostrophe (') first.
.You type '=1+1, cell shows: =1+1
----------------------------------------------
And he selects "$A$2,0,0,"
It doesn't let me do nothing.
How can I fix this error?
Thank you
I result a problem with this link
answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother-mso_2016/error-display-window-theres-a-problem-with-this/fd41b58f-de65-44a4-a086-cc829101c214
so I change (,) with (;)
And I have =offset('Item Search'!$A$2;0;0; counta('Item Search'!$A:$A)-1;5)
it works fine!
Good work Paulin. Yes the error they show i not helpful. The semi colon ; is used by Excel user in mainland Europe primarily.
Show!! +1
Super!