Search Engine in a User form

[nathandavies]

Hi All, I had a thread on here a couple of weeks ago which a member was helping me with but he is no longer able to help me due to other commitments.

This is the thread was "Search Multiple Worksheets using a User form and display in list box."

I have tried myself to complete the code for the "search engine" tool but I don't have enough experience or knowledge to complete.

What I’m trying to create is a user form that allows me to search through a sheets which is selected using a combo-box, and then a column is selected again using a combo-box, then the user can search for a phrase in a text box. This will then display the rows which match the “phrase” in a list box.

The thought is that once the correct line is found, there will be a command button which will copy that row or multiple rows to a worksheet named “Order Rec”

There will also be list box in the user form which displays all the information in the worksheet “order rec” and if a row has been inputted by mistake you can remove a row using another command button…

I have attached my worksheet with the user-form built with some code but it’s far from finished, the workbook i have uploaded is a condensed version of my actual product because it wont let me upload as its bigger than 1mb.

If anyone can help it would be greatly appreciated. LV SWITCHGEAR - Development Copy.xlsm

[mehmetcik]

Hi Nathan

What you require is quite simple.

So I will look at this for you.

However I must admit your proposed method is not very efficient.

I normally use three or more text boxes as my inputs.

for example 230v mcb Pro

would pull up any 230 volt Protek MCB

ex 2. 230v MCB would pull up all 230 Volt MCBs.

I would not need to bother with search columns, because the data I am after might not be in a specific column.

I will create a solution based on my ideas because I am probably more aware of the potential pitfalls than you are.

If it does not meet your requirements we can make some changes.




OK. Try this.

enter "int" into one text box, the search routine only works if you enter 3 characters

A list of descriptions containing Interlock pops up.

enter "for" for Fortress into one of the other text boxes and hey presto you have one option on display.

[nathandavies]

Hi
Thanks for your help on this and such a quick turn around! I will test it out on Monday when I get back into work.

Will it be easy to add additional worksheets to the code for searching?

Cheers Nathan

[nathandavies]

I have tested the userform this morning and the search function works fine but it only shows the first 4 columns ideally i would like it to show all the columns as the the first row of each work sheet, is this easy to do?


Also the command buttons don't seem to have any function to them? it wont allow me to add the selected part or multiple parts from the list box to the sheet "ORDER REC" and then display all contents in "ORDER REC" in the listbox below?

[nathandavies]

Can anyone help with this?

[mehmetcik]

Ok Nathan.

Try this version:-

[nathandavies]

Thanks for your help!

Everything works a treat! prefer the way you have populated the listbox and then can save it!.

The only issue i can see if that the list box is not showing all the columns of the rows when searching and its only copying 4 columns of each row to the order rec.

[mehmetcik]

It wasn't my intention to only copy 4 rows.

Although to be honest I only used 4 rows while testing.

Let me debug it and I'll get back to you.

I don't understand the comment about not showing all the columns.
I have it showing 6 columns, did you want more?


Ok, I have checked.

enter int into any of the text boxes,

7 rows will be displayed,

click on all 7 rows to highlight them and then click on "add to order".

now click on "save".

All 7 rows are saved.

[nathandavies]

When i run the search it only displays data from column A to F in the list box, ideally i would like it to show A to L then when i "save the order" it is only copying column A:F to the "ORDER REC" sheet but in the wrong columns (as per attached pic)

Untitled.png

[mehmetcik]

That is a lot of columns to display. but not impossible I think.

Do you really need those displayed in the listbox? or just copied into the order sheet?

Meanwhile I managed to simplify the code by using Collections Instead of Arrays to eliminate duplicate entries when transferring data between the list boxes.

I also added 4 more buttons for you.

Can you please advise what column to save the data too.

[nathandavies]

I think if we limit the list box to 'DESCRIPTION, MANUFACTURER, SUPPLIER, PART NUMBER, £ EACH' but then copy all the columns to the "ORDER REC"

when saving from the order rec list box, it should be the same as the original location. i think the best way to complete this is to copy the full row based on the text string found.

[mehmetcik]

Ok.

This is looking good.

[ I am learning a lot by doing this, which is great ].

I have modified my code so that you can have item 12 for example from several different source files in listbox2
And remove an item or save all items without confusion.

I had am using the line no and first 4 letters of the source file as a unique reference.

So the first 4 letters of your sheets need to be different. Obviously we could take more letters if needed.

However you need to work on your Workbook Layouts.
Not all sheets have the data in the same format

[nathandavies]

I have had a good play around with it and all the function seems to be working fine, apart from the listbox1 & 2 display is not showing the columns which i really need. ("DESCRIPTION, MANUFACTURER, SUPPLIER, PART NUMBER, £ EACH)

also when the "order rec" is saved it doesnt copy the full row, its still only copying a few cells of that row. for example if i search ABB - T1 / 16A / SP, it should copy the full row into the order rec including the formula in column K.

I'm not sure what you mean by workbook layouts? I thought ROW A on all sheets was the same. apart from Manufacturing Times, Elsteel, Copper & Summary. (these are not really relevant for searching).

[mehmetcik]

I wouldn't know which sheets you intend to search in. Never specified.

I am copying 10 columns. So K is column 11 easily ammended in the command button 3 click event.

Change the 10 to an 11

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[nathandavies]

I have changed the amount copying to 12 and it works a treat now!

just the small issue of the displaying the information in the listbox's. is it possible to display all 12 columns? i can get my head around the code at the moment it will probally take me a couple of weeks to work out what you have done!!

[nathandavies]

That's what I did, i'm just testing it in a bit more depth now.

[nathandavies]

I have found an issue with the "save" function, when you have more than 2 rows ("parts") shown and click save it only save's 2 entries not all of them.

[nathandavies]

Have you found a resolution for the "save order" problem and the listbox display. ( i have tried myself to change the display of the list box but i can manage to get all 12 columns or DESCRIPTION, MANUFACTURER, SUPPLIER, PART NUMMBER, £ EACH columns to display