CODEWITHSUNDEEP
excelvba
JoshL
JoshL

Reputation: 164

Selected row in listbox copied to another listbox

I have a listbox within a userform with a few rows of filtered data. I would like to be able to select one of the rows and have it appear in another listbox (just the values within that selected row, none of the others)

My current code is just:

Private Sub SelectHousingList_Click()
HousingList.Text = SelectHousingList.Selected(Row)
End Sub

With 'HousingList' being the listbox that I'd like the values to move to.

And 'SelectHousingList' being the rows of filtered data.

Previously in this code I've used code similar to this to select from a list of values (but not with a whole row of values).

Private Sub MaterialList_Click()
SelectedMaterialText.Value = MaterialList.Text
Worksheets("FSC PSC PFC").Range("D4").Value = SelectedMaterialText.Value
End Sub

The second line of code allows for the selected item in the list to be copied to a textbox.

If you need more of my code I can supply you with it.

This may be a simple question but I can't seem to find the answer anywhere or figure out code that allows it to happen.

Thank you in advance!

Upvotes: 2

Views: 858

Answers (2)

FaneDuru
FaneDuru

Reputation: 42256

This code retrieves in an array (or string) all the columns values on the selected row. It fills the second listbox with as many columns first one has:

Private Sub SelectHousingList_Click()
  Dim arrRow() As Variant, i As Long
  ReDim arrRow(Me.SelectHousingList.ColumnCount)
  For i = 0 To SelectHousingList.ColumnCount
    arrRow(i) = Me.SelectHousingList.List(i, Me.SelectHousingList.ListIndex)
  Next i
  Debug.Print Join(arrRow, ", ")
   With Me.HousingList
     .ColumnCount = Me.SelectHousingList.ColumnCount
     .AddItem arrRow(0)
      For i = 1 To UBound(arrRow)
          .List(.ListCount - 1, i) = arrRow(i)
      Next i
  End With
End Sub

Upvotes: 0

Brian M Stafford
Brian M Stafford

Reputation: 8868

If I understand your requirements, this should do what you need for a single column:

Private Sub SelectHousingList_Click()
   HousingList.AddItem SelectHousingList.Value
End Sub

If there are 2 columns, then this:

Private Sub SelectHousingList_Click()
   HousingList.AddItem SelectHousingList.List(SelectHousingList.ListIndex)
   HousingList.List(HousingList.ListCount - 1, 1) = SelectHousingList.List(SelectHousingList.ListIndex, 1)
End Sub

You'll need to add additional lines for every column beyond 2, changing the index for each one.

Upvotes: 2

Related Questions