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

Drobs

New Member
Joined
Aug 2, 2019
Messages
2
Hi All,

I am having some difficulty with my code, rare occurrence around here I'm sure :LOL:. 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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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:


b21f2cc2bdd66d59767e672d7a78c380.jpg




The code

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.
 
Upvote 0
Thank you for your help. Its amazing that people will go through all the trouble of clear examples just to help.
Thanks!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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