Pulling data from spreadsheet based on listbox selection

kjgallier

New Member
Joined
Sep 22, 2021
Messages
13
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a listbox which is populated with rows of information from a spreadsheet called Eque2_Contracts. Column B to be precise.

I now want to pull in data from other columns that are along the same row in the spreadsheet as the selected info/line in my listbox. I am going to use that data to populate a few different text boxes on the same form.

So for example, if I select a row in my listbox, it will find the data from column G of selected row (same spreadsheet) that is along the same row as listbox selection. I will then need to put that CELL data into a text box to display.

For example I may want to put other cell data along the same row in another text box on the same form and maybe a couple of other columns cell data same row also. (In other words I will need to add other cell data along the same row into different text boxes on same form)

ANY help would be much appreciated.
 

kjgallier

New Member
Joined
Sep 22, 2021
Messages
13
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Ok, this will handle textbox4
VBA Code:
Private Sub ComboBox1_Click()
   Dim Rws As Variant, Ary As Variant
  
   With Sheets("Eque2_Contracts")
      With .Range("A2:AB" & .Range("A" & Rows.Count).End(xlUp).Row)
         Rws = Filter(.Worksheet.Evaluate(Replace("transpose(if(@=""" & Me.ComboBox1.Value & """,row(@)-min(row(@))+1,""X""))", "@", .Columns(1).Address)), "X", False)
         If UBound(Rws) < 0 Then
            Exit Sub
         ElseIf UBound(Rws) = 0 Then
            ReDim Preserve Rws(1)
         End If
         Ary = Application.Index(.Value, Application.Transpose(Rws), Array(2, 4, 7, 8, 28, 19))
      End With
   End With
   Me.ListBox1.List = Ary
     
End Sub

Private Sub ListBox1_Click()
   With Me.ListBox1
      If IsError(.List(.ListIndex, 0)) Then Exit Sub
      Me.TextBox1 = .List(.ListIndex, 1)
      Me.TextBox2 = .List(.ListIndex, 2)
      Me.TextBox3 = .List(.ListIndex, 3)
      Me.TextBox4 = .List(.ListIndex, 4) - .List(.ListIndex, 5)
   End With
End Sub
Do you get any error messages, when selecting something from the listbox for Kelsall Housing
For info Text Box 4 is returning "0" no matter which one I select. Thank you!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

kjgallier

New Member
Joined
Sep 22, 2021
Messages
13
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Ok, this will handle textbox4
VBA Code:
Private Sub ComboBox1_Click()
   Dim Rws As Variant, Ary As Variant
  
   With Sheets("Eque2_Contracts")
      With .Range("A2:AB" & .Range("A" & Rows.Count).End(xlUp).Row)
         Rws = Filter(.Worksheet.Evaluate(Replace("transpose(if(@=""" & Me.ComboBox1.Value & """,row(@)-min(row(@))+1,""X""))", "@", .Columns(1).Address)), "X", False)
         If UBound(Rws) < 0 Then
            Exit Sub
         ElseIf UBound(Rws) = 0 Then
            ReDim Preserve Rws(1)
         End If
         Ary = Application.Index(.Value, Application.Transpose(Rws), Array(2, 4, 7, 8, 28, 19))
      End With
   End With
   Me.ListBox1.List = Ary
     
End Sub

Private Sub ListBox1_Click()
   With Me.ListBox1
      If IsError(.List(.ListIndex, 0)) Then Exit Sub
      Me.TextBox1 = .List(.ListIndex, 1)
      Me.TextBox2 = .List(.ListIndex, 2)
      Me.TextBox3 = .List(.ListIndex, 3)
      Me.TextBox4 = .List(.ListIndex, 4) - .List(.ListIndex, 5)
   End With
End Sub
Do you get any error messages, when selecting something from the listbox for Kelsall Housing
Ignore that, Text Box 4 value working fine. Just not the first combo box selection when selecting listbox items.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
Have you changed the code I supplied in anyway?
 

kjgallier

New Member
Joined
Sep 22, 2021
Messages
13
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Have you changed the code I supplied in anyway?


View attachment 47639
Have you changed the code I supplied in anyway?
Hi Fluff, no I havent changed your code, just been reading through it.
I understand you pulled in multiple columns of info into thie listbox here called costcode, but hid them which is great. But I have an issue, I need the cost code to not show duplicate codes in this list. I managed it for the combobox (job number) but I cant get the listbox to remove duplicate codes which would be column B where it gets the data from.

The reason I want to remove duplicates is because when the user clicks on code 8202 for example, those are 4 different suppliers, so supplier name (which will now need to be a listbox) should display all 4 suppliers.
They can they click a supplier and see ALL order numbers below (again a listbox) against that supplier. Selecting an order number should then show all descriptions on that order.

Cost updates depending on what is clicked.

I also need to add a "Select All" for cost code, supplier, and order number.


1) Starting with the first issue, removing duplicates from cost code? Which when clicked shows all suppliers in supplier name? THe code I used under ComboBox1 Change.. doesnt seem to like my code. I suspect it is because it is multicolumn info, just hidden.
Untitled-1.jpg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
As this is now a totally different question, it needs a new thread. Thanks
 

Forum statistics

Threads
1,147,821
Messages
5,743,393
Members
423,792
Latest member
travisds

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
Top