Populate rest of Userform based on two ComoboBox Values

si3po

New Member
Joined
Jan 7, 2019
Messages
45
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
Joined
Dec 3, 2018
Messages
8,897
Office Version
2007
Platform
Windows
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
Joined
Jan 7, 2019
Messages
45
***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
Joined
Dec 3, 2018
Messages
8,897
Office Version
2007
Platform
Windows
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
Joined
Oct 11, 2019
Messages
42
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
Joined
Dec 3, 2018
Messages
8,897
Office Version
2007
Platform
Windows
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.
 

Forum statistics

Threads
1,082,333
Messages
5,364,675
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top