Populate rest of Userform based on two ComoboBox Values

[si3po]

Hey all,

I am trying to quickly get order data from a single sheet of over 3000 entries using a UserForm to make it as simple as possible for staff. So far i've made a userform that i want to use containing a number of text boxes: "Cust ID", "Order #", "Item", "Part No", "Date Ordered", "Status"

I currently have the userform set so that as i type the Customer ID into into ComboBox1, my ComboBox2 auto-updates with all the order numbers associated to that customer ID. On selecting the required order number in ComboBox2 and pressing a command button, i would like the a number of TextBoxes on the UserForm to populate based on the selections made in the two comboboxes.

For example,

[TABLE="width: 500"]
<tbody>[TR]
[TD]CustId[/TD]
[TD]Order Number[/TD]
[TD]Item[/TD]
[TD]Part No[/TD]
[TD]Date Ordered[/TD]
[TD]Status[/TD]
[TD]Expect Del[/TD]
[TD]Multi-part Order[/TD]
[/TR]
[TR]
[TD]Franks123[/TD]
[TD]AB123[/TD]
[TD]FLANGE[/TD]
[TD]123456[/TD]
[TD]12-7-19[/TD]
[TD]IN ORDER[/TD]
[TD]N/K[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Smith999[/TD]
[TD]ZQ456[/TD]
[TD]HOOK[/TD]
[TD]14557[/TD]
[TD]21-4-19[/TD]
[TD]DEL DUE[/TD]
[TD]FRIDAY[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Jones145[/TD]
[TD]HP445[/TD]
[TD]KNOB[/TD]
[TD]457544[/TD]
[TD]23-2-19[/TD]
[TD]AWAIT BUY[/TD]
[TD]N/K[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Smith999[/TD]
[TD]CU124[/TD]
[TD]SCREW[/TD]
[TD]4564744[/TD]
[TD]18-4-19[/TD]
[TD]AWAIT BUY[/TD]
[TD]N/K[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Smith999
[/TD]
[TD]ZQ124[/TD]
[TD]HOOK[/TD]
[TD]47735[/TD]
[TD]17-8-19[/TD]
[TD]DEL DUE[/TD]
[TD]FRIDAY[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Franks123[/TD]
[TD]HP687[/TD]
[TD]SCREW[/TD]
[TD]36697[/TD]
[TD]12-6-19[/TD]
[TD]AWAIT BUY[/TD]
[TD]N/K[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Jones145[/TD]
[TD]AB124[/TD]
[TD]FIXING[/TD]
[TD]368759[/TD]
[TD]30-3-19[/TD]
[TD]IN ORDER[/TD]
[TD]N/K[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Smith999[/TD]
[TD]ZQ554[/TD]
[TD]HOOK[/TD]
[TD]68857[/TD]
[TD]19-2-19[/TD]
[TD]IN ORDER[/TD]
[TD]N/K[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]


ComboBox1 = Smith999
ComboBox2 = ZQ456

would fill the following textboxes:

Item: Hook
Part No: 14557
Order Date: 21-4-19
Status: Del. Due

As you can see, there may be multiple orders of the same/similar parts for each customer, so that is why i am using the CustomerID & Order Number to quickly find the order status. So far i've been looking around and have seen a few VBA VLOOKUP examples, but these only work with a single criteria.

Can anyone assist with some VBA that might help use multiple criteria to fill my text boxes please??

 

[DanteAmor]

If the combination of CustId and Order Number is unique, so use the following:

Private Sub CommandButton1_Click()
  Dim sh As Worksheet, r As Range, f As Range, cell As String, wRow As Long
  TextBox1.Value = ""
  TextBox2.Value = ""
  TextBox3.Value = ""
  TextBox4.Value = ""
  '
  If ComboBox1 = "" Or ComboBox1.ListIndex = -1 Then
    MsgBox "Select ID"
    ComboBox1.SetFocus
    Exit Sub
  End If
  If ComboBox2 = "" Or ComboBox2.ListIndex = -1 Then
    MsgBox "Select Order"
    ComboBox2.SetFocus
    Exit Sub
  End If
  Set sh = Sheets("Sheet5")
  Set r = Sheets("Sheet5").Range("A:A")
  Set f = r.Find(ComboBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      If f.Offset(, 1).Value = ComboBox2.Value Then
        wRow = f.Row
        Exit Do
      End If
      Set f = r.FindNext(f)
    Loop While Not f Is Nothing And f.Address <> cell
  End If
  If wRow > 0 Then
    TextBox1.Value = f.Offset(, 2)
    TextBox2.Value = f.Offset(, 3)
    TextBox3.Value = f.Offset(, 4)
    TextBox4.Value = f.Offset(, 5)
  Else
    MsgBox "ID - Order does not exist"
  End If
End Sub

 

[si3po]

***SOLVED*** Re: Populate rest of Userform based on two ComoboBox Values - SOLVED

@DanteAmor - you sir are a squire and a gent, this works perfectly. I've modified it slightly to search the correct columns and sheets, but it's perfect, thank you!

 

[DanteAmor]

Re: ***SOLVED*** Re: Populate rest of Userform based on two ComoboBox Values - SOLVED

It is a pleasure to help you. I appreciate your kind words. Thanks for the feedback.

 

[tynawg]

Re: ***SOLVED*** Re: Populate rest of Userform based on two ComoboBox Values - SOLVED

Hello,
I think I have the same, or close to, query but my code is different. Can I post here my query?
Than you

 

[DanteAmor]

Re: ***SOLVED*** Re: Populate rest of Userform based on two ComoboBox Values - SOLVED

Hello,
I think I have the same, or close to, query but my code is different. Can I post here my query?
Than you

Hi @tynawg, If the code is not what you need or cannot adapt the code to your solution, it is recommended that you create a new thread and there explain in detail what you have and what you expect from the result.