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

 

[si3po]

Actually, you would need another textbox as well. The listbox would be auto-populated when the userform is initialized. The user selects a header and the macro would search for the header in row 1 to return the column number. Then the user would enter the search criterium based on that column in the new textbox. The macro would search for that criterium in the column and fill in the rest of the userform. I hope that makes sense. I will wait for the revised file.

Hi Mumps,

if that's what it needs... i'm not sure how you want to set it up, but the revised file has been uploaded here - its an *.xlsb format with the UserForm attached as requested. Feel free to add/amend the UserForm as you see fit for the purposes required.

 

[mumps]

Click here for your file. Click the "Search" button on the "DEMANDS" sheet. Select a search column, enter the search criterium and click the "Search" button on the form. I noticed that some of the fields were not populated. We may have to modify the code to fine tune any changes you may need. Please describe any necessary changes. I have deleted a few of the macros in your original file. If you need them, please explain in detail what you want them to do and we can go from there. Hopefully, this is a good first step.

 

[si3po]

Click here for your file. Click the "Search" button on the "DEMANDS" sheet. Select a search column, enter the search criterium and click the "Search" button on the form. I noticed that some of the fields were not populated. We may have to modify the code to fine tune any changes you may need. Please describe any necessary changes. I have deleted a few of the macros in your original file. If you need them, please explain in detail what you want them to do and we can go from there. Hopefully, this is a good first step.

Mumps, that's fabulous, and certainly a starter for 10. I've had a quick look into it and, whilst it certainly will be a starter, i'd like to implement a partial match and allow users to cycle through any matches, e.g. if they selected 'Part Number' and put "123" as the criterium, it would return back matching results 'acb123', '123xyz', '123' and '123456' and display them individually with an tow buttons for 'next' and 'previous' at the bottom to cycle through the different matches.

Also, is it possible to specify the columns used as search criteria in the ComboBox?

 

[mumps]

display them individually with an tow buttons for 'next' and 'previous' at the bottom to cycle through the different matches.

I'm not sure what you mean by this. Could you explain in more detail?

 

[si3po]

I'm not sure what you mean by this. Could you explain in more detail?

For example - a user selects 'Part Number' from the drop-down and inputs a partial search term, e.g. "123".

Within the Sheet there are multiple part numbers with '123' in them, currently the form doesn't allow for the user to cycle through the possible hits for the correct demand. So, by selecting, 'Part Number' and putting in a search term, again for argument "123" the userform would display the first match in the form and a 'next' button at the bottom of the form (not yet implemented) would become enabled allowing the user to find the next match, on moving to the next match a 'previous' button would also become enabled allowing the user to cycle back to the last match.

i hope that gives a good idea of what i mean?

also, unrelated to that, when loading the form i've found that the code 'Breaks' out and won't run again unless i click 'run' in the VBA Editor screen - meaning that the user will be unable to have the Search or Exit buttons work.

 

[mumps]

I think that approach would be somewhat problematic because you would need a way to determine the next match or previous match based on the current match. There may be a way to do that but I honestly don't know how that could be done. Currently, the search combobox is populated with all the columns headers. If we go back to using only the fields below using comboboxes, we could have only those fields populated with multiple values and then the user could select one of those values to populate the rest of the form. If I can get that to work, would that approach be OK?

Demand number - Col A
Part Number - Col D
End User (Section) - Col S
Inventory Code (AinU) - Col U
Tail Code - Col P
ADF / LIM - Col Y
SNOW ref - Col Z

Also, column S has "A/C Systems Affected" not "End User (Section)",(above in red). Could you clarify?

the code 'Breaks' out

How does the code "break out"?

 

[si3po]

If we go back to using only the fields below using comboboxes, we could have only those fields populated with multiple values and then the user could select one of those values to populate the rest of the form. If I can get that to work, would that approach be OK?

That's worth looking at, but please don't do any more than you think is necessary. The Prev/Next function is really a 'nice to have' and by no means a 'must have' for this endeavour.

Also, column S has "A/C Systems Affected" not "End User (Section)",(above in red). Could you clarify?

Correct, that should read End User (Section) - Col T.

si3po said:
the code 'Breaks' out

How does the code "break out"?

ignore me, it's working perfectly this morning! But basically, yesterday when i launched the form with the button, the form loaded as expected, I choose the search type (e.g. Part No) and entered my search detail, clicked the 'Search' button and nothing happened. Click the 'Exit' button, again nothing happened. Got o VBA window, the 'run' symbol is green, so i clicked it and the form worked as expected.

 

[si3po]

Hi Mumps - i've been looking about for some code that might help us implement a prev / next button and cycle through the various matches that may happen and came up with this post here on the forums: link

it's not exactly the same thing as i'm looking to do, but is definitely leaning that way.

 

[mumps]

Click here for your file.
Click the "Search" button on the DEMANDS sheet. Select a "Column to Search" and then a "Criterium". Click the "Search" button. The seven comboboxes will be populated with all the values based on the search criterium. You can then select any value in any combobox to populate the userform based on that selection. The comboboxes remain populated with all the original values so you can select a different value. I think this approach takes care of your request for finding the "previous" or "next" values. Give it a try and let me know how it works out.

 

[si3po]

Click here for your file.
Click the "Search" button on the DEMANDS sheet. Select a "Column to Search" and then a "Criterium". Click the "Search" button. The seven comboboxes will be populated with all the values based on the search criterium. You can then select any value in any combobox to populate the userform based on that selection. The comboboxes remain populated with all the original values so you can select a different value. I think this approach takes care of your request for finding the "previous" or "next" values. Give it a try and let me know how it works out.

Afternoon Mumps, really appreciate this, and it's working great in the dummy file so thank you. However when I copy it across (and amend the ComboBox 'addItem' entries to fit my need) to the actual spreadsheet for use, I'm getting a "'Run-time error '91' Object Variable or With Block not set" with this line:

Set crit = sh.Columns(colFnd.Column).find(TextBox1.value, , xlValues, xlWhole)

see the below screenshot for details:

edit: just to add it fails with the same error no matter what ComboBox1 value is chosen.