Populating Listbox/ Textbox based on criteria or listbox selection

K

Kaarrma

New Member
Joined
Jun 8, 2018
Messages
4
I have a userform that i need a listbox filled with 2 columns from another sheet based on the contents of a Textbox in the userform
I have the Textbox populated, and can fill the listbox column 1
I then need to populate 2 other textboxes based on the selection made in the listbox

TextBox1 holds a claim number (Claim Number is in column C in the source sheet called "WRS Worked Hrs")
Each claim number links to a range of dates (The same date bay be linked to multiple claim numbers)
ListBox1 is then populated with the dates linked to the claim number in TextBox1

Based on the date selected in the ListBox1, i need TextBox3 and TextBox4 populated with the corresponding data from the source sheet from columns E and F (This is to allow the user to change these 2 values and then be able to save the updated values to the source sheet)

Column G in the source sheet holds a unique identifier (since the dates may link to several claim numbers, and claim numbers may link to several dates)
Unique identifier is a Concat ("Claim Number" , _ , "Date") (With the date in Excel serial format)

I have gotten as far as populating the list box with the dates, but as these are not unique values, TextBox3 and TextBox4 are populating with values from the first found instance of that date
I do have a TextBox5 that can hold the unique identifier if needed to populate the other textboxes

This is the code i have so far:

VBA Code: 
Private Sub UserForm_Activate()
Dim rngEmpM As Range
Dim rngListM As Range
Dim strSelectedM As String
Dim LastRowM As Long

TextBox1.Value = ThisWorkbook.Worksheets("WRS P1").Range("O1").Value
TextBox2.Value = ThisWorkbook.Worksheets("WRS P1").Range("U5").Value

        ListBox1.ColumnCount = 2

           strSelectedM = ThisWorkbook.Worksheets("WRS P1").Range("O1").Value
          
           LastRowM = Worksheets("WRS Worked Hrs").Range("C" & Rows.Count).End(xlUp).Row
 
           Set rngListM = Worksheets("WRS Worked Hrs").Range("C2:C" & LastRowM)
 
           For Each rngEmpM In rngListM
 
                 If rngEmpM.Value = strSelectedM Then
 
                      Me.ListBox1.AddItem rngEmpM.Offset(, 8)
                    
 
                 End If
 
           Next rngEmpM


End Sub



Private Sub ListBox1_Click()
Dim ws As Worksheet
Dim iRow As Long



Set ws = ThisWorkbook.Worksheets("WRS Worked Hrs")
With Me
    iRow = Me.ListBox1.ListIndex + 4

    .TextBox3.Value = ws.Cells(iRow, 2)
    .TextBox4.Value = ws.Cells(iRow, 6)

End With

           

End Sub

Below is a sample of the data i the source sheet (WRS Worked Hrs)
Column C has the claim number
Column D has the date list
Column E has the data for TextBox3 and column F has the data for TextBox4
Column G has the unique identifier for each entry
[/CODE]

Excel Formula: 
[TABLE]
[TR]
[TD]Emp ID[/TD]
[TD]Employee[/TD]
[TD]Claim[/TD]
[TD]Week Start[/TD]
[TD]Hrs Work[/TD]
[TD]Paid $[/TD]
[TD]Week ID[/TD]
[/TR]
[TR]
[TD][RIGHT]1234[/RIGHT][/TD]
[TD]Bug, June MS[/TD]
[TD]3894797[/TD]
[TD][RIGHT]14/06/2021[/RIGHT][/TD]
[TD][RIGHT]8[/RIGHT][/TD]
[TD][RIGHT]442.68[/RIGHT][/TD]
[TD]3894797_44361[/TD]
[/TR]
[TR]
[TD][RIGHT]1234[/RIGHT][/TD]
[TD]Bug, June MS[/TD]
[TD]3894797[/TD]
[TD][RIGHT]21/06/2021[/RIGHT][/TD]
[TD][RIGHT]16[/RIGHT][/TD]
[TD][RIGHT]505.92[/RIGHT][/TD]
[TD]3894797_44368[/TD]
[/TR]
[TR]
[TD][RIGHT]1234[/RIGHT][/TD]
[TD]Bug, June MS[/TD]
[TD]3894797[/TD]
[TD][RIGHT]28/06/2021[/RIGHT][/TD]
[TD][RIGHT]16[/RIGHT][/TD]
[TD][RIGHT]505.92[/RIGHT][/TD]
[TD]3894797_44375[/TD]
[/TR]
[TR]
[TD][RIGHT]1234[/RIGHT][/TD]
[TD]Bug, June MS[/TD]
[TD]3894797[/TD]
[TD][RIGHT]5/07/2021[/RIGHT][/TD]
[TD][RIGHT]16[/RIGHT][/TD]
[TD][RIGHT]584.97[/RIGHT][/TD]
[TD]3894797_44382[/TD]
[/TR]
[TR]
[TD][RIGHT]2345[/RIGHT][/TD]
[TD]Bloggs, Joey MS[/TD]
[TD]3857810[/TD]
[TD][RIGHT]14/06/2021[/RIGHT][/TD]
[TD][RIGHT]12[/RIGHT][/TD]
[TD][RIGHT]347.76[/RIGHT][/TD]
[TD]3857810_44361[/TD]
[/TR]
[TR]
[TD][RIGHT]2345[/RIGHT][/TD]
[TD]Bloggs, Joey MS[/TD]
[TD]3857810[/TD]
[TD][RIGHT]21/06/2021[/RIGHT][/TD]
[TD][RIGHT]12[/RIGHT][/TD]
[TD][RIGHT]347.76[/RIGHT][/TD]
[TD]3857810_44368[/TD]
[/TR]
[TR]
[TD][RIGHT]2345[/RIGHT][/TD]
[TD]Bloggs, Joey MS[/TD]
[TD]3857810[/TD]
[TD][RIGHT]28/06/2021[/RIGHT][/TD]
[TD][RIGHT]20[/RIGHT][/TD]
[TD][RIGHT]597.8[/RIGHT][/TD]
[TD]3857810_44375[/TD]
[/TR]
[TR]
[TD][RIGHT]2345[/RIGHT][/TD]
[TD]Bloggs, Joey MS[/TD]
[TD]3857810[/TD]
[TD][RIGHT]5/07/2021[/RIGHT][/TD]
[TD][RIGHT]20[/RIGHT][/TD]
[TD][RIGHT]597.8[/RIGHT][/TD]
[TD]3857810_44382[/TD]
[/TR]
[TR]
[TD][RIGHT]3456[/RIGHT][/TD]
[TD]Happy, Sappy MS[/TD]
[TD]3087863[/TD]
[TD][RIGHT]14/06/2021[/RIGHT][/TD]
[TD][RIGHT]17[/RIGHT][/TD]
[TD][RIGHT]727.26[/RIGHT][/TD]
[TD]3087863_44361[/TD]
[/TR]
[TR]
[TD][RIGHT]3456[/RIGHT][/TD]
[TD]Happy, Sappy MS[/TD]
[TD]3087863[/TD]
[TD][RIGHT]21/06/2021[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]3087863_44368[/TD]
[/TR]
[TR]
[TD][RIGHT]3456[/RIGHT][/TD]
[TD]Happy, Sappy MS[/TD]
[TD]3087863[/TD]
[TD][RIGHT]28/06/2021[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]3087863_44375[/TD]
[/TR]
[TR]
[TD][RIGHT]3456[/RIGHT][/TD]
[TD]Happy, Sappy MS[/TD]
[TD]3087863[/TD]
[TD][RIGHT]5/07/2021[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD]3087863_44382[/TD]
[/TR]
[TR]
[TD][RIGHT]4567[/RIGHT][/TD]
[TD]Doe, John MR[/TD]
[TD]3887940[/TD]
[TD][RIGHT]14/06/2021[/RIGHT][/TD]
[TD][RIGHT]26[/RIGHT][/TD]
[TD][RIGHT]1181.01[/RIGHT][/TD]
[TD]3887940_44361[/TD]
[/TR]
[TR]
[TD][RIGHT]4567[/RIGHT][/TD]
[TD]Doe, John MR[/TD]
[TD]3887940[/TD]
[TD][RIGHT]21/06/2021[/RIGHT][/TD]
[TD][RIGHT]37[/RIGHT][/TD]
[TD][RIGHT]1490.88[/RIGHT][/TD]
[TD]3887940_44368[/TD]
[/TR]
[TR]
[TD][RIGHT]4567[/RIGHT][/TD]
[TD]Doe, John MR[/TD]
[TD]3887940[/TD]
[TD][RIGHT]28/06/2021[/RIGHT][/TD]
[TD][RIGHT]38.5[/RIGHT][/TD]
[TD][RIGHT]1393.65[/RIGHT][/TD]
[TD]3887940_44375[/TD]
[/TR]
[TR]
[TD][RIGHT]4567[/RIGHT][/TD]
[TD]Doe, John MR[/TD]
[TD]3887940[/TD]
[TD][RIGHT]5/07/2021[/RIGHT][/TD]
[TD][RIGHT]40[/RIGHT][/TD]
[TD][RIGHT]1328.04[/RIGHT][/TD]
[TD]3887940_44382[/TD]
[/TR]
[/TABLE]
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Emp IDEmployeeClaimWeek StartHrs WorkPaid $Week ID
1234​
Bug, June MS3894797
14/06/2021​
8​
442.68​
3894797_44361
1234​
Bug, June MS3894797
21/06/2021​
16​
505.92​
3894797_44368
1234​
Bug, June MS3894797
28/06/2021​
16​
505.92​
3894797_44375
1234​
Bug, June MS3894797
5/07/2021​
16​
584.97​
3894797_44382
2345​
Bloggs, Joey MS3857810
14/06/2021​
12​
347.76​
3857810_44361
2345​
Bloggs, Joey MS3857810
21/06/2021​
12​
347.76​
3857810_44368
2345​
Bloggs, Joey MS3857810
28/06/2021​
20​
597.8​
3857810_44375
2345​
Bloggs, Joey MS3857810
5/07/2021​
20​
597.8​
3857810_44382
3456​
Happy, Sappy MS3087863
14/06/2021​
17​
727.26​
3087863_44361
3456​
Happy, Sappy MS3087863
21/06/2021​
0​
0​
3087863_44368
3456​
Happy, Sappy MS3087863
28/06/2021​
0​
0​
3087863_44375
3456​
Happy, Sappy MS3087863
5/07/2021​
0​
0​
3087863_44382
4567​
Doe, John MR3887940
14/06/2021​
26​
1181.01​
3887940_44361
4567​
Doe, John MR3887940
21/06/2021​
37​
1490.88​
3887940_44368
4567​
Doe, John MR3887940
28/06/2021​
38.5​
1393.65​
3887940_44375
4567​
Doe, John MR3887940
5/07/2021​
40​
1328.04​
3887940_44382
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Submitted Data from a Userform is not Posting to a ListBox
2 3
Replies
20
Views
2K
Robert_Conklin
R
A
  • Question
Need help with vba codes for Excel 2010 Userform Combobox, ListBox, TextBox and Commands
Replies
0
Views
425
AliCain
A
O
  • Question
"database form": populate textbox with value based on listbox selection.
Replies
2
Views
5K
shandy367
S
S
  • Question
Userform listbox selection populates two text boxes
Replies
1
Views
3K
bensonsearch
bensonsearch

Forum statistics

Threads
1,214,827
Messages
6,121,823
Members
449,049
Latest member
cybersurfer5000

Share this page

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