PDA

View Full Version : Create UserForm with Vlookup (combobox and textbox)



shre0047
03-07-2019, 12:56 PM
I want to create a user-form with a combo box (CB1) and 2 text boxes (TB1 & TB2). Based on the input for CB1 and TB1, the output will show in TB2.

For CB1, there would be 4 different values:


Private Sub UserForm_Initialize()
CB1.AddItem "Search DB1"
CB1.AddItem "Search DB2"
CB1.AddItem "Search DB3"
CB1.AddItem "Search DB4"
End Sub

And depending on which value is selected in CB1, it would trigger a vLookup value of TB1 into a different sheet, and input a value into TB2.

I'm fairly green in the user-form feature so looking for guidance. Thanks for any help.

Pseudocode:
If 'Search DB1' selected, find TB1 value in sheet1, column B, and return value into TB2
If 'Search DB2' selected, find TB1 value in sheet2, column Q, and return value into TB2
If 'Search DB3' selected, find TB1 value in sheet3, column T, and return value into TB2
If 'Search DB4' selected, find TB1 value in sheet4, column A, and return value into TB2

Rob342
03-07-2019, 02:16 PM
Hi shre0047
What is input into TB1 are they values, text or a mixture ?
is it possible to provide a sample workbook with the data?

shre0047
03-08-2019, 07:40 AM
Hi Rob342,
See sample file (https://www.amazon.com/clouddrive/share/4nO9QPmII5vJ3YaBQMYWVIteZZWKNolLcojAYmhfPMb).

When a user hits the button to launch the userform, there will be four options in the combo box:
1. Old R ID
2. New R ID
3. Old T ID
4. New T ID

If 'Old R ID' is selected, user provides a number in SampleIDText textbox. When user clicks Search, it will search the number in Table2, find the name, then search for the same name in Table3, and output the ID into SampleIDTextReturn.

For example, in SampleIDText, user picks 13049. In Table2, it finds 13049 with the name being 'Name15'. Then in Table3, it finds name 'Name15' and outputs ID 70 into SampleIDTextReturn.

The other 3 dropdowns would be the same way, but different combinations of the tables.

Rob342
03-08-2019, 08:43 AM
I'm presuming or have I got it wrong ?
Old R Id =Table 1
New R ID = Table 2
Old T ID = Table 3
New T ID =Table 4

shre0047
03-08-2019, 09:29 AM
Old R Id = Pull ID from Table2, find in Table3 via name match, and return id from Table3
New R ID = Pull ID from Table3, find in Table2 via name match, and return id from Table2
Old T ID = Pull ID from Table1, find in Table4 via name match, and return id from Table4
New T ID =Pull ID from Table4, find in Table1 via name match, and return id from Table1

大灰狼1976
03-08-2019, 09:57 PM
Hi shre0047!
Please refer to the Attachment.

snb
03-09-2019, 06:47 AM
I'd prefer:

shre0047
03-12-2019, 09:31 AM
Thank you both for your help! They worked great

shre0047
03-12-2019, 09:45 AM
Hi shre0047!
Please refer to the Attachment.

If I wanted to enhance this form to include status via textboxes (WF1Status & WF2Status), would I simply alter the code to include new ranges?

So when i provide the ID, WF1Status would return the Status for the ID, and WF2Status would return the status for the ID in the new Table worksheet.

大灰狼1976
03-12-2019, 06:26 PM
I think it shouldn't be hard to achieve.
Please give me an example(workbook).

shre0047
03-15-2019, 07:43 AM
I've updated the file you provided with the details.
https://www.amazon.com/clouddrive/share/tboqSuZJJ6aBrFzo1dPX8oGkHcUh7XfPd2nxF22m78l

大灰狼1976
03-17-2019, 06:05 PM
Sorry, Due to network restrictions, it is not possible to open amazon website. Can you post it here.