Populate rest of Userform based on two ComoboBox Values

si3po

Board Regular
Joined
Jan 7, 2019
Messages
98
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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??
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
***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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top