Pulling data from spreadsheet based on listbox selection

[kjgallier]

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]

In other words, I need to take my current listbox selection, which would have always come from column B and get the program to grab the info directly across from this in say column D cell whatever, and post that data into a text box. Achievable?

 

[Fluff]

One way would be to pull all the relevant columns into the listbox (you can hide the columns) & then pull the data from the listbox.
Which columns do you want?

 

[kjgallier]

One way would be to pull all the relevant columns into the listbox (you can hide the columns) & then pull the data from the listbox.
Which columns do you want?

Thank you.
So my listbox is populated by the following (thanks to you) It finds my combobox value in the whole spreadsheet Column A and returns a list of adjacent values in Column B each time it finds it in Column A. It then lists them in the listbox.

Dim Ary As Variant

With Sheets("Eque2_Contracts")
With .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row)
Ary = Filter(.Worksheet.Evaluate("transpose(if(" & .Columns(1).Address & "=""" & Me.ComboBox1.Value & """," & .Columns(2).Address & ",char(2)))"), Chr(2), False)
End With
End With
Me.ListBox1.List = Ary

I now need to select a row in the listbox (which is data from Column B) and find cell values for column D (post in textbox1) G (post in textbox2) H (post in textbox3) and the Column AB - S and post in textbox 4 (AB and S) are monetary values.

ANY help would be much appreciated.

 

[Fluff]

Not sure what you mean regards "Column AB - S" but this will do the other textboxes.

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))
      End With
   End With
   Me.ListBox1.List = Ary
      
End Sub

Private Sub ListBox1_Click()
   With Me.ListBox1
      Me.TextBox1 = .List(.ListIndex, 1)
      Me.TextBox2 = .List(.ListIndex, 2)
      Me.TextBox3 = .List(.ListIndex, 3)
   End With
End Sub

Private Sub UserForm_Initialize()
   With Me.ListBox1
      .MultiSelect = fmMultiSelectSingle
      .ColumnCount = 5
      .ColumnWidths = "100,0,0,0,0"
   End With
End Sub

 

[Fluff]

Forgot to add something to the listbox event, it should be

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)
   End With
End Sub

 

[Fluff]

Thinking about it, does this "Column AB - S and post in textbox 4" mean you want textbox4 to have the value from col S subtracted from the value in col AB?

 

[kjgallier]

Thinking about it, does this "Column AB - S and post in textbox 4" mean you want textbox4 to have the value from col S subtracted from the value in col AB?

Thank you very much. The code almost works perfectly. Just one thing that doesn't. See picture attached.
When I select Kelsall Housing in my dropdown box (left) it only loads the info for the first row of listbox. When I click on the next one down in listbox, it does not update the textboxes, BUT if I select Jacques way or ANY other one from the combo box, it works fine. Just Kelsall housing doesnt seem to work.
Also, yes column AB MINUS column S will go into another text box 4. It will be a value £ - your help is much appreciated and I enjoying this!! And trying to understand what the code is doing ha ha.

 

[Fluff]

Ok, this will handle textbox4

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

 

[kjgallier]

Ok, this will handle textbox4

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

Thank you very much! No, no errors, it just doesn't update the textboxes when selecting any of them. But all other combobox selections work fine. I was just looking over the code but I havent done this for a long time so understanding it is a little tricky. Especially as its pulling in data from columns D G H etc and I cant even see D G H listed ha ha.