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,306
The problem we have here is that I can't see the original file as I mentioned to you at the beginning. When you get the error and hover the mouse over "colFnd.Column" what message do you see?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
The problem we have here is that I can't see the original file as I mentioned to you at the beginning. When you get the error and hover the mouse over "colFnd.Column" what message do you see?
sorry, that was my issue, i neglected to change the row where the column headers began before crying 'help!'... changed those and it's all good now....i think
 

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
Glad it worked out. :)

It's great, thank you! There's a few things i'm going to add and/or shift about (we decided on a couple of different search types), i want to add a message box that appears after the search button is clicked to tell the user to select an item from the ComboBoxes when a match is made, and i am going to disable all ComboBoxes other than the search type before the search button is clicked; but otherwise it's more than doing what i asked, thanks so much for your help!

Also, whilst it's not happening for other users, and it's probably not related at all, but i seem to be getting Run-Time 1004 errors every second time i run the form. Example: open the form, complete a search and it works flawlessly. Close the form, reopen and search yields a run-time 1004 error, close, reopen and search again, another run-time 1004 error. I save and closed the workbook, re-open it, open the form to search - no error and the search runs OK; but when I close the form, then reopen it to search, it yields a run-time 1004 error again and won't continue until i close and re-open the workbook from scratch.

Is there a way i can clear all values in memory when it opens the sheet, or a button to clear all values on the sheet from memory when i click 'exit'?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,306

ADVERTISEMENT

but i seem to be getting Run-Time 1004 errors every second time i run the form
I have tried several times and cannot reproduce the error you describe using the sample file. Are you getting the error in the sample file or your actual file. Which line of code is highlighted when you click "Debug"?
 

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
I have tried several times and cannot reproduce the error you describe using the sample file. Are you getting the error in the sample file or your actual file. Which line of code is highlighted when you click "Debug"?
In the actual file itself, not the sample.

it's always on this line - the same as before:
VBA Code:
Set colFnd = sh.Rows(15).find(ComboBox1.value, , xlValues, xlWhole)

So row 15 is where my headers are input, and I've checked they're all entered exactly as typed in the headers, and that the correct 'case' code sections are entered for the ComboBox possible entries in the very first step.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,306

ADVERTISEMENT

I'm afraid that I can't offer a suggestion without seeing the file or a de-sensitized version as you did before.
 

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
I'm afraid that I can't offer a suggestion without seeing the file or a de-sensitized version as you did before.
thanks for the kind offer, but i think i'll keep debugging it from here for the time being with another user and their account and the live document.

I find it very odd that i seem only to encounter the issue on my own user account with the live document, and only after it has been executed once already - your file works no issues whatsoever. Stranger still, some times the search completes, i can see the item i've searched for in the worksheet but the userform has no values.

would any of these issues be caused by SharePoint and Auto-Save running on the sheet in the background at all - just a thought??
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

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