CODEWITHSUNDEEP
vbaexcel
Oday Salim
Oday Salim

Reputation: 1147

Inserting selected row in ListBox to Sheet

My ListBox is a 4 column ListBox and I am inserting the last 3 columns of data within.

I managed to put together a code that inserts the nth row of ListBox into the appropriate cells in the spreadsheet. However, what I would like is to insert whichever row a user selects from the ListBox... And if they choose nothing, it will not insert anything.

My code for inserting nth row is:

Dim LastRow As Long
Dim n as integer
LastRow = Sheets("Sheet1").Cells(Rows.count, "F").End(xlUp).row
n = inputbox("enter value")
With Worksheets("Sheet1")
    .Range("F" & LastRow + 1) = ListBox1.Column(1, n)
    .Range("G" & LastRow + 1) = ListBox1.Column(2, n)
    .Range("H" & LastRow + 1) = ListBox1.Column(3, n)
End With

Can you please advise on how to modify my code to choosing the selected value rather than nth? And how to not insert if not selected?

Upvotes: 0

Views: 2012

Answers (1)

Eliot
Eliot

Reputation: 623

If you only allow one item to be selected, then you can simply reference the .Value of your listbox element.

Dim LastRow As Long
Dim n as integer
LastRow = Sheets("Sheet1").Cells(Rows.count, "F").End(xlUp).row
n = ListBox1.Value
With Worksheets("Sheet1")
    .Range("F" & LastRow + 1) = ListBox1.Column(1, n)
    .Range("G" & LastRow + 1) = ListBox1.Column(2, n)
    .Range("H" & LastRow + 1) = ListBox1.Column(3, n)
End With

If you allow multiple items to be selected, then you will need to loop through each item in the listbox and use .Selected(n) to determine if the item is selected.

Dim LastRow As Long
Dim n as integer
LastRow = Sheets("Sheet1").Cells(Rows.count, "F").End(xlUp).row
For n = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(n) Then
        With Worksheets("Sheet1")
            .Range("F" & LastRow + 1) = ListBox1.Column(1, n)
            .Range("G" & LastRow + 1) = ListBox1.Column(2, n)
            .Range("H" & LastRow + 1) = ListBox1.Column(3, n)
        End With
    End If
Next n

Upvotes: 2

Related Questions