Litsbox, search function, return data

**Leamc** · 11-24-2011, 09:33 AM

Hi to all the excel experts!

What started out as an easy listbox function from the standard developer toolbar in excel has now turned into quite a major project me.

What I want to achieve is to have a search function where the user can search for an asset in a table by typing any part of the description. (the standard function is not working as I am working with 4500 records and it is not efficient to scroll though all of the items)

The result should be to return all the data in the table that is a full or partial match to the search criteria.

The user should then be able to click on one of the returned options which should return the asset number to a specified cell in the sheet.

As I can gather from all the searches I have done, this solution will have to be devoloped in VBA. I undertsand the process, but am an absolute novice when it comes to coding.

It would be much appreciated if someone can give me an example from which I can work or some help in this regard.

Thanks in advance
Leamc

**nilem** · 11-24-2011, 10:05 AM

Perhaps this example will help you.

**Leamc** · 11-24-2011, 12:54 PM

Nilem, thank you very much for the reply. The searchfuntion is excactly what I am looking for.

The only thing that I need to change is that instead of the cursor jumping to the chosen result field, I need to return the value in column B corresponding to the result field to a fixed cell, say $C$1.

The other problem is that I cant read your code.

Thanks for the effort and maybe I can work from here.

**Leamc** · 11-25-2011, 12:04 AM

Bump no response

**nilem** · 11-25-2011, 12:24 AM

Simply replace two procedures. In the form module:

Please Login or Register  to view this content.

in the sheet module:

Please Login or Register  to view this content.

Do you need a translation of comments in the code?

**Leamc** · 11-25-2011, 04:06 AM

Nilem, this is going to work perfect.

Just need a pointer or two. Got the userform working from VBA using F5. What is the procedure to call the form from excel.

What part of the code do I need to change, to change the range of the lookup. I need to change the column from A to C and limit the search to sheet1.

Please Login or Register  to view this content.

Is this correct?

Thank you very much for the help so far, this is really appreciated!!

**nilem** · 11-25-2011, 04:32 AM

Please Login or Register  to view this content.

Here's a simplified version (see attachment)

**Leamc** · 11-25-2011, 07:09 AM

Nilem, I got everything working except that I want the output also to be in Sheet2. I thus need o change this code to include a sheet reference

Please Login or Register  to view this content.

Then I still dont know how to launch the form from excel.

Thank you very much for the help so far, I am really excited about the outcome!!

**nilem** · 11-25-2011, 07:37 AM

try it

Please Login or Register  to view this content.

Look at my last example is attached. There is a green button that shows the form. Code assigned to this button:

Please Login or Register  to view this content.

I wrote the code for myself why so immodestly named macro

**Leamc** · 11-25-2011, 08:26 AM

Nilem, thank you very much, everything is working 100%. Thanks for the patience!!

Litsbox, search function, return data

LinkBack

Thread Tools

Rate This Thread

Display

Litsbox, search function, return data

Re: Litsbox, search function, return data

Re: Litsbox, search function, return data

Re: Litsbox, search function, return data

Re: Litsbox, search function, return data

Re: Litsbox, search function, return data

Re: Litsbox, search function, return data

Re: Litsbox, search function, return data

Re: Litsbox, search function, return data

Re: Litsbox, search function, return data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions