Populate rest of Userform based on two ComoboBox Values

si3po

New Member
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,

CustIdOrder NumberItemPart NoDate OrderedStatusExpect DelMulti-part Order
Franks123AB123FLANGE12345612-7-19IN ORDERN/KY
Smith999ZQ456HOOK1455721-4-19DEL DUEFRIDAYY
Jones145HP445KNOB45754423-2-19AWAIT BUYN/KN
Smith999CU124SCREW456474418-4-19AWAIT BUYN/KN
Smith999
ZQ124HOOK4773517-8-19DEL DUEFRIDAYY
Franks123HP687SCREW3669712-6-19AWAIT BUYN/KN
Jones145AB124FIXING36875930-3-19IN ORDERN/KN
Smith999ZQ554HOOK6885719-2-19IN ORDERN/KN

<tbody>
</tbody>


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

Well-known Member
If the combination of CustId and Order Number is unique, so use the following:

Code:
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

New Member
***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!
 
Last edited:

DanteAmor

Well-known Member
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

New Member
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

Well-known Member
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.
 

Some videos you may like

This Week's Hot Topics

Top