+ Reply to Thread
Results 1 to 2 of 2

How to copy selected listbox item to sheet

  1. #1
    Registered User
    Join Date
    07-14-2005
    Posts
    2

    How to copy selected listbox item to sheet

    I have a listbox with 5 columns placed in row 1 on a worksheet.
    This listbox have a number of items that the user can select from.
    I want to copy the selected index from the listbox to a row on the worksheet.


    I made this small code for a dobbel click on the listbox and with this code I can copy a single cell from the selected item in the listbox to a cell on the worksheet.
    And then the next row on the worksheet is selected.


    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim rg As Range
    Dim rg1 As Range

    Set rg = Application.ActiveCell

    If rg.Row < 2 Then Exit Sub

    Set rg1 = Cells(rg.Row, 1)
    rg1 = ListBox1.List(ListBox1.ListIndex)

    Set rg1 = Cells(rg.Row + 1, 1)
    rg1.Select
    ListBox1.Activate

    End Sub


    But I want to copy all data in the selected item (meaning all columns) and not just the first column.
    How can this be done ??

  2. #2
    K Dales
    Guest

    RE: How to copy selected listbox item to sheet

    Where you have this line: rg1 = ListBox1.List(ListBox1.ListIndex)
    substitute this:
    With rg1
    For i = 0 to 4
    .Offset(0,i).Cells(1,1).Value = ListBox1.Column(i)
    Next i
    End With

    --
    - K Dales


    "michdan" wrote:

    >
    > I have a listbox with 5 columns placed in row 1 on a worksheet.
    > This listbox have a number of items that the user can select from.
    > I want to copy the selected index from the listbox to a row on the
    > worksheet.
    >
    >
    > I made this small code for a dobbel click on the listbox and with this
    > code I can copy a single cell from the selected item in the listbox to
    > a cell on the worksheet.
    > And then the next row on the worksheet is selected.
    >
    >
    > Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    > Dim rg As Range
    > Dim rg1 As Range
    >
    > Set rg = Application.ActiveCell
    >
    > If rg.Row < 2 Then Exit Sub
    >
    > Set rg1 = Cells(rg.Row, 1)
    > rg1 = ListBox1.List(ListBox1.ListIndex)
    >
    > Set rg1 = Cells(rg.Row + 1, 1)
    > rg1.Select
    > ListBox1.Activate
    >
    > End Sub
    >
    >
    > But I want to copy all data in the selected item (meaning all columns)
    > and not just the first column.
    > How can this be done ??
    >
    >
    > --
    > michdan
    > ------------------------------------------------------------------------
    > michdan's Profile: http://www.excelforum.com/member.php...o&userid=25218
    > View this thread: http://www.excelforum.com/showthread...hreadid=387080
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1