UserForm List and Copy to Clipboard

[snowmansmartie]

Hi guys,

I have a spreadsheet that is 20 columns wide and can be x amount of rows long.

Once the fields have been populated for a row or number of rows I have a button that will copy that row data to the clipboard to paste into our ticketing system.

I have the button and when you click it it fires a userform with a list box on it. I have populated the list box with the data in column B, this is so that the user can select which row they need to copy to the ticket.

Once row has been selected there is a button on the UserForm which should then copy the row data to the clipboard.

When I click the copy button I am just firing a MsgBox at the moment to confirm what has been selected and this works, so far so good.

Now heres where my problem begins, how do I then take the selected item from the listbox, grab the related row data from the spreadsheet and copy it to the Clipboard?

Form Init:

Private Sub UserForm_Initialize()

    Dim Rng As Range
            
            With ThisWorkbook.Sheets("Sheet2")
                
                Set Rng = .Range(.Cells(9, 2), .Cells(.Rows.Count, 2).End(xlUp))
                                
            End With
            
            With Me.ListBox1
                .RowSource = Rng.Address
                .BoundColumn = 1
                .ColumnHeads = False
            End With

End Sub

Form Button:

Private Sub CommandButton1_Click()

    Dim SelectedItem As String
    Dim ListItem As String
    Dim Rng1 As Range
    
    SelectedItem = ListBox1.Value
    ListItem = ListBox1.ListIndex + 1
    
    With ThisWorkbook.Sheets("Sheet2")
               
        Set Rng1 = .Range(.Cells(ListItem, 1), .Cells(.Rows.Count, 2).End(xlUp))
        Range(Rng1).Copy

    End With
    
    MsgBox ("You choose - " & SelectedItem)
    
End Sub

As you can see from above I have tried using the var ListItems as a cell ref, but it doesnt seem to like this either.

This seems such an easy thing to do, but I just can't get me head around it.

Any help would be appreciated

Thanks

James

 

[Yongle]

This logic should get you on the right path

Your list box is filled from

ThisWorkbook.Sheets("Sheet2").Range(.Cells(9, 2), .Cells(.Rows.Count, 2).End(xlUp)

If the first item is selected, that item originated from row 9

The array of list box items starts at 0 (ie the first item on the list is ListItem(0)
- adding 9 to the ListItem index number gives the row number

Private Sub CommandButton1_Click()
    Dim i As Long, r As Long
    
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            r = i + 9
            ThisWorkbook.Sheets("Sheet2").Range("A" & r, "D" & r).Copy
            Exit Sub
        End If
    Next i
End Sub

 

[snowmansmartie]

Yongle, that did the trick.

Thank you, and I knew it would be simple.

James