Populate Userform TextBox with search result

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
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.

1614349370261.png


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

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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.
VBA Code:
Set crit = sh.Columns(4).find(ptnum.value, , xlValues, xlWhole)
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,898
Messages
5,627,526
Members
416,250
Latest member
darius_rebelo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top