VBA- Inex Matchdex Match?

[CuriousGeorg]

Hi guys, (apologies for the title.. not sure what happened there.. was ok until I clicked save)

I have a table of data which is three columns wide and hundreds and hundreds of rows in length.

What I need is a user form that if I type a Number in say Textbox1..

It finds that number in column A then returns Column B in textbox2 and Column C in Textbox 3.

How do I code this in VBA?

[snb]

see e.g. http://www.snb-vba.eu/VBA_Userform_in_database_en.html

[CuriousGeorg]

Thanks SNB but doesn't really help much. All the columns are in excel. The userform will open blank.. a user will type in a number (which is what the search criteria will be). then click the button. Its on this click I need the VBA to fire using textbox 1 as search criteria..

[Aussiebear]

Hmmmm... seems to me that you might need something to be called (other code) on completion of the textbox entry.

Maybe...

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        CommandButton1_Click
    End If
End Sub

Then you use a Vlookup formula to find the text entry within the table of data.

[mperrah]

vbax53854.xlsm
Sorry late in the game, but here is a userform and sheet solution.
I used a search button to change label captions based on selection from the list. (have the search item selected when you click search)
The userform loads all three columns but hides the last 2, then updates the captions when you click search.

On the sheet I used a data validation list and index/match to find the other 2 column values.

hope this helps

-mark

[CuriousGeorg]

Thanks Mark, that actually does a wonderful job. Least I know it can do what I want it to do. Ill have a play with the list bit as I think the user wants to be able to type his own search value, but that's easily done.

[mperrah]

vbax53854_v2.xlsm
Here you go.
This adds a command button to load the form.
The close button code is added.
I hid the listbox that loads the sheet values
and added a text box to let the user type their own number,
then the search button starts a looks for a match of the input and the hidden listbox values first column.
then displays the matches adjacent columns as requested.

Hope this helps
- I also removed the drop down from the sheet so it works the same way as the new form...

-mark