Populate Userform TextBox with search result

[si3po]

hi all, i'm hoping this is a relatively easy one...but it seems to have confused me no end.

I have built a UserForm (see picture) that is intended to allow users to search the entire workbook using a keyword placed in the provided search box - i.e. a user could enter any one of part number, serial number, registration code etc.

On clicking the 'search' button, I want the users search result(s) to populate the various text boxes with the relevant details found from the search row. Now, I have managed to disable and colour all the text boxes so they cannot be typed in, and on clicking the 'search' button these are re-enabled and coloured white. My next step is to complete the search code, and whilst i've had some success in the past with searching, that has only been for a value in a single column, not across multiple columns in all worksheets within the workbook.

Does anybody have any suggestions or possible solutions i could look into please??

Thanks in advance

Si

 

[mumps]

is it possible to have the 'search' button clear the form before running the search function?

The way the macro is written, this wouldn't work. I think the best way is just to add another command button perhaps labelled "Clear Data" and write the code to clear all the objects in the form. If you need help with this, please let me know.

also, multiple matches seems to cause the UserForm to break with the following:

The error you're getting with this line of code should not be caused by multiple matches because it looks only for the first occurrence of ptnum.

Set crit = sh.Columns(4).find(ptnum.value, , xlValues, xlWhole)

 

[si3po]

The way the macro is written, this wouldn't work. I think the best way is just to add another command button perhaps labelled "Clear Data" and write the code to clear all the objects in the form. If you need help with this, please let me know.

No i done this already, i figured the same after reading through the code. I inserted a 'Reset' button to unload and show the form again.

I'll try again with the multiple matches, but that was the line causing the error... i'll go have another look and report back.