Search through database and display results in userform

**karthikcoep** · 01-17-2009, 01:33 PM

I have a database of 13 columns and ever increasing rows.
I want the following to be there on my userform.

1 combobox: showing the list of categories from which to search.(The categories are the column headings in columns A1 to A13.The user will have to select one category.

1 textbox:Here the user will enter the search term.

1 Command Button: When the command button is clicked/entered, the code shall be such that it will search in the column corresponding to the category mentioned in the combobox and display the results( The entire 1 row x 13 cloumns containing the search term) in a Listbox. If the search term does not matches then a message box should appear with the message "No entries found"
[B]

1 Listbox:to display the search result as mentioned above.

Thanks in advance for the help

**Leith Ross** · 01-18-2009, 05:01 AM

Hello karthikcoep,

You can copy your data onto "Sheet1" of the attached workbook. Since it searches the active sheet, no need to worry about changing the sheet name. All 13 columns are visible in the ComboBox and the ListBox. I enhanced the search features to include matching by case or ignoring case, match the whole word or only part, and do a single search or search for all matches. The UserForm also acts like a standard window. You can minimize and maximize it to and from the task bar. Let me know how this works for you.

Sincerely,
Leith Ross

**MickG** · 01-18-2009, 08:55 AM

Hi, I can't compete with Leith , but attached is a Simpler Version.
Replace the test Data with your Data and also the column Headings (If there not too long !).
Leith, Have you got a good starting point for API Calls ??
Regards Mick

**lyzieaa_mkj88** · 08-18-2011, 04:21 AM

Hi I am very new to Excel/VBA. I have created a "JV INFORMATION SYSTEM" in excel and have designed a user form for users to enter new JV iNFO.

My next step is to design a Search or find form for viewing the JV Information where
user can choose to look up for JV info either by Company Name or Project Name or Date

The worksheet that stored my data is called "JV" . In the sheet "JV " I have following 10columns

Column A -No
Column B-Company Name
Column C Project Name
Column D- Start Date
Column E- End Date
Column F- Equity Ratio
Column G- Company Address
Column H-Contact Person
Column I-Contact No

I did see a few examples of look up forms but am struggling to customise them to suit me.

Ideally I need a combobox & textbox in search field. So user can choose the "Search by" category using combobox e.g. Company Name or Project Name or Date
and then enter the relevent text in the textbox to carryout the search.

and then use labels & listbox to display the related fields on the form. The reason I prefer labels is that I do not want users to edit the info. and listbox to show multiple results out of which user can choose specific one....e.g. when user does a search by Company, I want listbox to show the various Information about that company or when user search by date e.2 by Year , all information related to that year is display in the list box .

any help would be appreciated...thx in advance

**royUK** · 01-18-2009, 03:07 PM

See the database form example

http://www.excel-it.com/vba_examples.htm

**Leith Ross** · 01-18-2009, 04:51 PM

Hello karthikcoep,

If you really don't need all the bells and whistles of a UserForm to find, replace, add, and delete entries, here is simple and direct macro that will launch Excel's built-in database editor.

Sub Run()
    ActiveSheet.ShowDataForm
End Sub

Sincerely,
Leith Ross

**karthikcoep** · 01-20-2009, 11:10 AM

Hey thanks to everyone.Sorry for the late reply.
@Leith: The file works well.

**karthikcoep** · 01-22-2009, 02:48 PM

Hi Leith;
Your form works pretty well.
But I need it modifed.
I have tried a lot but not able to do so.
I want only the following.
User selects a criteria from the combobox(Column 1 ro 13, Row 1)
User types text in search field and then hits the search button
In the list box all the results are listed. For each result there is only one single row.
The top row of the listbox displays the 1row of the sheet.(That I can do)
The results are displayed one below the other. So that the user need not use "prev" and "next".
And the most important is when there are no results,a message box must pop up saying "No Results found".
Thanks

**Leith Ross** · 01-22-2009, 09:24 PM

Hello karthikcoep,

I have redone the UserForm. The ListBox has been replaced with a ListView control. The ListBox had to many limitations to do what you wanted. The version of this control works with Excel 2000 and up. If you plan to run this on earlier systems then some changes will have to be made to load the correct control. The searches are not case sensitive and match whole words only. All matches appear in the ListView with the row number and the values for columns "A" to "M".

Sincerely,
Leith Ross

**arthurbr** · 10-11-2011, 05:10 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

**gkaty28** · 05-24-2012, 04:10 AM

is it possible that the searched item can be link to a file? i want to link the search item directly to the file.

**arlu1201** · 05-24-2012, 05:08 AM

Gkaty28,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.