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,305
Which text box are you using as the criteria for the search? In which column of the "DEMANDS" sheet is this criteria found?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
Which text box are you using as the criteria for the search? In which column of the "DEMANDS" sheet is this criteria found?
i was hoping that any one of the 'enabled' fields could be used, i.e. i only have a part number or i only have a inventory code, or a demand reference. For reference, all these 'enabled' fields are Combo Boxes rather than Text Boxes, in case multiple options are found.

the 'enabled' search data found in the spreadsheet as follows:

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

I'm sorry i can't be any more specific or provide a file, i understand how much harder that has made this cry for help.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,305
Since you have a mixture of text boxes and comboboxes, it's very hard to determine how the comboboxes will be poplulated since they can contain more than one value. Also, it sounds like you don't have one particular textbox or combobox that would act as the search criterium. Would you be searching based on more than one criterium?All this makes it very difficult without seeing what the "DEMANDS" sheet looks like and how the data is organized.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
im afriad that i can't provide a sample file as i've been told it would be a breach of our Cyber SOPs.
You have shared screen shots of your project userform & code behind it on public forum so presumably if your SOPs allow this then ask if creating a completely anonymised copy of your workbook & placing on filesharing site (from your home connection if not allowed at work) would be an issue?

When giving assistance with complex projects, it makes it much easier if a copy of the workbook can be provided.

Dave
 

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Since you have a mixture of text boxes and comboboxes, it's very hard to determine how the comboboxes will be poplulated since they can contain more than one value. Also, it sounds like you don't have one particular textbox or combobox that would act as the search criterium. Would you be searching based on more than one criterium?All this makes it very difficult without seeing what the "DEMANDS" sheet looks like and how the data is organized.

Apologies, yes there is a mixture, but there doesn't have to be a mixture of TextBox and ComboBox items on the search form, i only did this as i thought it would help to select from a list of possible matches...

Again, i'm fully aware that the lack of any sort of data table is really hampering efforts and i really appreciate your help. In a bid to help as much as possible, i'm still wrestling with the powers that be to get a completely desensitised data table put together so that you might get an idea of what we use, but that could be a way off just yet.

With regards which search criteria to use, this is the problem. I cannot rely on a user knowing any one specific criteria - e.g. they might only have a demand number or a part number or a tail code for example. Therefore whichever one of these boxes had data input would be the search criteria.

If it isn't possible to have multiple search boxes (but only one criteria in use at any time), then would it be possible to have a single search text box that would be able to search through all criteria columns and show these within the designed form at all, maybe using a 'Previous' / 'Next' button to cycle through matches that are found?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,305
I believe that everything you want to do is possible. I don't need the entire "completely desensitised data table". I would just need 5 or 6 rows of data.
 

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I believe that everything you want to do is possible. I don't need the entire "completely desensitised data table". I would just need 5 or 6 rows of data.

Hi Mumps.

It took some doing, but basically i the only way i could give you the information needed (according to our SyOps Dept) was to create you a whole new sheet with dummy information in it. All columns are placed where they appear on my actual sheet and are called the same things, the only difference is my data begins from row 18 due to a number of buttons required at the top of the sheet.

i have a GitHub account, so hopefully you'll be able to access the file? here is the link,
please let me know if you can't access it and i'll try something else.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,305
Thank you for the file. Since you can't rely on a user knowing any one specific search criteria, my suggestion would be to insert a listbox on the userform. This listbox would be populated with all the column headers. The user would select the header on which to base the search. If this would work for you, it would be helpful if you could add the userform to the file you uploaded and attach the link to the revised file. Then I will be able to add the listbox and test a possible solution using the actual userform. I know you went through a lot of trouble to created the de-sensitized file so I apologize for asking you to upload a revised file which includes the userform. I think that once you do this, the solution should not be too difficult.
 

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
Thank you for the file. Since you can't rely on a user knowing any one specific search criteria, my suggestion would be to insert a listbox on the userform. This listbox would be populated with all the column headers. The user would select the header on which to base the search. If this would work for you, it would be helpful if you could add the userform to the file you uploaded and attach the link to the revised file. Then I will be able to add the listbox and test a possible solution using the actual userform. I know you went through a lot of trouble to created the de-sensitized file so I apologize for asking you to upload a revised file which includes the userform. I think that once you do this, the solution should not be too difficult.
hi again.

Yes a listbox seems a plausible solution... but how would that work exactly? As for the userform, i'll add that to the file and replace the existing one at GitHub when i'm back in the office tomorrow - the link should be the same.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,305
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,556
Messages
5,637,035
Members
416,954
Latest member
Gohar hussain

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