Search Function Using Input Textbox

**tommib** · 05-20-2009, 05:44 AM

Hi Guys.

At the moment, I have a search spreadsheet where I click a button, and an input box appears where I put a car reg in, and it searches a different sheet, returning results on the row the reg belongs in.

I've had a slight change in spec. I now need the same function to work, but instead of an input box.. I want the user to type the reg in a text box that is already on the sheet, and then click a search button to retrieve the results.

Is there anyway to easily amend the following code to get there?

Please Login or Register  to view this content.

Thanks in advance!!

**tommib** · 05-20-2009, 10:19 AM

Can anyone help?

**T-J** · 05-20-2009, 02:59 PM

Replace varRegID with TextBox1 (or whatever it is called) in the line where you find the data.

**tommib** · 05-21-2009, 04:36 AM

That didnt work TJ.. it doesnt do anything?

**T-J** · 05-21-2009, 10:23 AM

ok try the following. I've made a few amendments to your code, using Range objects instead of Selection. You will have to change any worksheet names and commandbutton names as required.

The code copies data to the same row (16) each time so overwrites existing data, I don't know if that is what you want, but your old code appears to do this.

There are 2 examples of a search, one using a textbox and one using an inputbox.

Place in a worksheet module:

Please Login or Register  to view this content.

Place in a code module:

Please Login or Register  to view this content.

**tommib** · 05-22-2009, 10:43 AM

Thats brilliant! It works a treat thank you so much!

Is it possible to have the retrieved data placed on different rows instead of just row 16?

For example..

Search range - result range

B2 in C9

B3 in C11

B4 in C13

Hope this makes sense!

**T-J** · 05-22-2009, 02:51 PM

I think I got it, if not you may have to adjust the numbers (in the Set RangeToCopy line) to get the block of cells you want to copy.

Comment out this line in the code above,

Please Login or Register  to view this content.

and insert these lines in the same place.

Please Login or Register  to view this content.

**tommib** · 05-26-2009, 07:02 AM

That didnt seem to work.

It ended up putting the whole row on Row105!.

I've attached the sheet.. hopefully it will be easier to understand where I want the data.

Thank you so much once again!!

**T-J** · 05-26-2009, 10:57 AM

Sorry, I misunderstood what you wanted, the workbook has helped a lot.
Please replace the whole of Sub FindReg with the following code:

Please Login or Register  to view this content.

**sweetrevelation** · 05-28-2009, 06:44 AM

Man, that code is incredible. I have a question in the worksheet 1. How do you put a text box within a cell like that? Also, how do you put the button to the right like that? Thanks. I wish I had skills like that.