Search through database and display results in userform

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

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

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.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

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

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

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

Hey Thanks a lot Leith: This works like charm except that I have 4 sheets in my workbook.
Out of that only one sheet contains the data that i need to search.
So I tried
Sheets(1).Select
But it is not working
Can u suggest how to use ur file if there are multiple sheets.

Hello karthikcoep,

I have made the changes to the attached workbook to search only the worksheet pointed to by Sheets(1).

Sincerely,
Leith Ross

Hello Leith,
The file u sent is running fine,
but when i export the userform and import it back to use in my file it is not working. The gridlines are not visible.There is not error but in the listview control only the row no is displayed and nothing else.
Please help.
I tried this on a blank workbook then also it is not working.

Hello karthikcoep,

The problem is the reference library isn't loaded on the other machines. The workbook I posted has the reference saved. I have added a macro that will automatically load the library reference when the workbook is opened. Here is the auto loading macro which is called by the Workbook_Open() event procedure. This macro is located in Module1.

Please Login or Register  to view this content.

Workbook Open Event - ThisWorkbook Module

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Thanks a lot Leith.
May god bless u!!!!!!!!!!!!!

Leith,
you've come sooo close to solving an project that has stumped me for weeks!
in my woorksheet (see attached) i'm trying to search list (M) and chose from search list to return to cell D5. "D5:D55" will have this ability. Can you take me this last step?
thank you,
sick

Hello Leith Ross, I found the program you wrote absolutly useful to a project that I am working on right now that has over 2500 product. I am wondering though, is there a way to change the code slighly to select the condensed data in the user box, and have it return to the worksheet highlighted. The only reason I would as Is that I would use a similar program so that the user could get to that row to access pdf forms that are embeded in each row? Any ensight would be lovely.

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

we have 300 employee's details in sheet 1 (passport details / visa details/ labour details/ personal details/ salary details/ leave details) in sheet 2 need user form if i enter emp id then i have to get all details of emp. in sheet 2

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.

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