Updating labels in a userform based on User Selections and existing Datasheet

[Drobs]

Hi All,

I am having some difficulty with my code, rare occurrence around here I'm sure . I would like to fetch data from a spreadsheet and update the caption of a label based on 3 criteria: 2 ComboBox selections and 1 Textbox entry. Basically, I want to find a value that meets 3 criteria and report it to the user for their own knowledge. Anyone have any idea where I should start with this? I have attached an Imgur link with a look at my current form and an example of the data I am trying to look through. Currently, I have the userform so that when the user selects the Department they are looking through captions update to show what factory lines are in that department. I want to report the output of that line on a specific date and shift for the user's convenience.

Thanks for being a great community for someone always wanting to learn more and a student just scratching the surface!

https://imgur.com/a/z63rZP1

 

[DanteAmor]

In your image where it represents the sheet, they are not the same data that you put in the following images, therefore, it is not clear your example.

But I understand what you need.
The best thing is to perform the search and the result put it in a listbox.


Assuming your data as follows:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:131.17px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DEPARTMENT</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">SHIFT</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATE</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">ASSET</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">QTY</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">43</td><td style="text-align:right; ">1</td><td style="text-align:right; ">02/jul/2019</td><td >AS1</td><td style="text-align:right; ">23</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">43</td><td style="text-align:right; ">1</td><td style="text-align:right; ">02/jul/2019</td><td >AS2</td><td style="text-align:right; ">24</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">43</td><td style="text-align:right; ">1</td><td style="text-align:right; ">02/jul/2019</td><td >AS3</td><td style="text-align:right; ">25</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">46</td><td style="text-align:right; ">2</td><td style="text-align:right; ">02/jul/2019</td><td >AS4</td><td style="text-align:right; ">26</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">46</td><td style="text-align:right; ">2</td><td style="text-align:right; ">02/jul/2019</td><td >AS4</td><td style="text-align:right; ">26</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">46</td><td style="text-align:right; ">2</td><td style="text-align:right; ">02/jul/2019</td><td >AS4</td><td style="text-align:right; ">26</td></tr></table>

The form would look like this:

The code

Private Sub ComboBox1_Change()
  Call UpdateListbox
End Sub
Private Sub ComboBox2_Change()
  Call UpdateListbox
End Sub
Private Sub TextBox1_Change()
  Call UpdateListbox
End Sub


Sub UpdateListbox()
  Dim sh As Worksheet, cb1 As Variant, cb2 As Variant, cb3 As Variant, i As Long
  Set sh = Sheets("Sheet1")
  ListBox1.Clear
  If IsNumeric(ComboBox1) Then cb1 = Val(ComboBox1) Else cb1 = ComboBox1
  If IsNumeric(ComboBox2) Then cb2 = Val(ComboBox2) Else cb2 = ComboBox2
  If IsDate(TextBox1) Then cb3 = CDate(TextBox1) Else cb3 = TextBox1
  
  For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
    If sh.Cells(i, "A") = cb1 And sh.Cells(i, "B") = cb2 And sh.Cells(i, "C") = cb3 Then
      ListBox1.AddItem sh.Cells(i, "D")
      ListBox1.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, "E")
    End If
  Next
End Sub


Private Sub UserForm_Activate()
  ListBox1.ColumnCount = 2
End Sub

Check the operation and I adapt it to your needs.

 

[Drobs]

Thank you for your help. Its amazing that people will go through all the trouble of clear examples just to help.
Thanks!

 

[DanteAmor]

Thank you for your help. Its amazing that people will go through all the trouble of clear examples just to help.
Thanks!

I'm glad to help you. Thanks for the feedback.