Excel search box with VBA

Hi guys,

I have some knowledge of excel macros but what I am attempting to do I don't even know where to start with, so I was hoping you guys could help

I've got a spreadsheet with a worksheet that has about 1500 accounts on, 1 per line, and the columns are 'account number', 'account name', 'yearly sales' etc..

On another tab there is a space for an account number to be added and the sheet gets populated, in an easy to read fashion, so that sales reps on the road can see a snapshot of that accounts info..

I want to change this to make it more user friendly..

I would like a button called 'open' and when you click on it a box pops up where reps could either enter an account number and bring up an accounts details or start typing in the account name and it starts auto populating with what it thinks the account you're looking for is (because most of the time you don't know off the top of your head what the account number is).. The accounts in question are pubs so their are going to be a lot of red lions and three horseshoes etc which may make this more problematic..

Firstly is this possible and what's the best method of doing it??


Sorry that there's not any more info I can give..

Hopefully someone out there can help me


Cheers,

Thanks Mehmet..

I'll look forward to your reply. Once you get me started the rest of it should hopefully start making sense. It's all part of the learning process ...

Hi Mehmet,

Just had a brief look at userforms..

The one thing that's stumping me is, say I design the userform with a Text box for account number and text box for account name.. I want to search for 'The Crown' and there are 5 crowns in the list of accounts. I want it to then bring up all those 5 options so I can click the one I want (haven't the foggiest how this can be done)...

I assume the userform will sit in one tab and once I add an account number or account name, a macro will run which will populate all the relevant fields in another tab and switch me directly into that tab..


Cheers,

You could do your search via cascading comboboxes - they drill down each related subject.
As per file attached.
torachan.

Another option:
Here's an example of data entry using userform + searchable listbox.
It's not what you want but if you're interested in this method I can amend it to suit your requirement.

How it works:
- Double-click a cell in the table (where you want to insert data) to open the userform.
- In any texbox you can type some keywords to search, e.g "ma la", the listbox will show all matching data.
- Use Down Arrow or Tab to move the cursor to the Listbox.
- Hit Enter or double-click to insert the selected data into the active cell.
- To continue entering data to the next cell press Alt+Down arrow OR hit NEXT button (or press Alt+Up arrow OR hit PREV button to enter data in the previous cell)

data entry via listbox 2a.jpg

Hi guys,

Thanks for all your help..

All alternatives will work well but I think Mehmet's fits the bill on what I want to achieve..

What I'd like is the same userform with the three text/combo boxes..

The first combo/textbox linking to Account Names, the second combo/text box linking to Postcodes and the third combo/text box linking to 'account number..
with 'OK' and 'Cancel' buttons also.

So this pops up and a user can either enter an account number and press OK.. The account number is then populated on sheet 'name of cust' in cell 'A4' and an already written macro called 'Go' is automatically run, which brings up all that accounts info in a record card.

Alternatively a user can enter account name and then go to combo box 2 and have a choice of postcodes that match the name of that account. There may be 5 Royal Oaks but the account manager will know the location of the one he wants and know what postcode matches..

Once the correct postcode is picked from the dropdown then the account number automatically populates into the final text box and you click 'OK' and voila..


I've had an attempt this morning with no real success, unless I sit down for hours and learn this stuff (which I will do) I'm going to struggle to get this done by the deadline of tomorrow...


If it's an easy change to what you've already sent Mehmet


Cheers,

Anyone .....

Attached your file - combos are now working - you will have to put the code back on the two command buttons - then sort out what you are trying to do ???
For the combos to work the data has to be formatted as 'text' because the process works by 'splitting strings'.
torachan.

Hi Torachan,

Thanks for your reply..

Is there another way of doing it apart from splitting strings as formatting the order number as text means that other formulas stop working?


Cheers,