+ Reply to Thread
Results 1 to 4 of 4

ListBox column data transfer to another ListBox

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    ListBox column data transfer to another ListBox

    I have a form with two ListBoxes (ListBox1 & ListBox2). The code to initialize ListBox1 is below, where a named range (SheetA) in entered into cell A1:
    Private Sub UserForm_Initialize()
    ‘Prepare ListBox
    With listBox1
    .ColumnCount = 3
    .ColumnWidths = "50;100;100"
    .RowSource = ""
    End With
    ‘Generate list from named range
    Dim SheetA As Range
    Set SheetA = Range("A1") 'This is a named range
    Dim counter As Long
    Dim totalRows As Long
    totalRows = Range(SheetA).Rows.Count
    counter = 0
    Do
    With Me.listBox1
    counter = counter + 1
    ''create a new row with Additem
    .AddItem Range(SheetA)(counter, 1).Value
    ''add item in second column of a row
    .List(.ListCount - 1, 1) = Range(SheetA)(counter, 1).Offset(0, 1).Value
    ''add item in third column of a row
    .List(.ListCount - 1, 2) = Range(SheetA)(counter, 1).Offset(0, 2).Value
    End With
    Loop Until counter = totalRows
    End Sub
    There is a CommandButton to move selection from ListBox1 to ListBox2, code below:
    Private Sub cmdMoveSelRight_Click()
      Dim iCnt As Integer
      'Move Selected Items from Listbox1 to Listbox2
        	For iCnt = 0 To Me.listBox1.ListCount - 1
            		If Me.listBox1.Selected(iCnt) = True Then
                		Me.listBox2.AddItem Me.listBox1.List(iCnt)
           		 End If
        	Next
     	For iCnt = Me.listBox1.ListCount - 1 To 0 Step -1
            		If Me.listBox1.Selected(iCnt) = True Then
               		 Me.listBox1.RemoveItem iCnt
           		 End If
       	 Next
    End Sub
    The first code works fine and generates a 3 column list. But the second code only transfers the first column into ListBox2. How can the code be altered to transfer all 3 columns from ListBox1 to ListBox2? Thanks Sandy

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: ListBox column data transfer to another ListBox

    Try this.
    Private Sub cmdMoveSelRight_Click()
    Dim iCnt As Integer
    Dim iCol As Integer
    
        'Move Selected Items from Listbox1 to Listbox2
        For iCnt = 0 To Me.listBox1.ListCount - 1
            If Me.listBox1.Selected(iCnt) = True Then
                Me.listbox2.AddItem
                For iCol = 0 To listBox1.ColumnCount - 1
                    Me.listbox2.List(Me.listbox2.ListCount - 1, iCol) = Me.listBox1.List(iCnt, iCol)
                Next
            End If
        Next
        
        For iCnt = Me.listBox1.ListCount - 1 To 0 Step -1
            If Me.listBox1.Selected(iCnt) = True Then
                Me.listBox1.RemoveItem iCnt
            End If
        Next
        
    End Sub
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: ListBox column data transfer to another ListBox

    Hi Norie I got the code to work. Thanks for giving me the clues!

  4. #4
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: ListBox column data transfer to another ListBox

    Thanks Norie that worked.
    Would you help with code to shift selected items in ListBox2 back to ListBox1? Currently I have:
    Private Sub cmdMoveSelLeft_Click()
    
    'Variable Declaration
        Dim iCnt As Integer
                
        'Move Selected Items from Listbox2 to Listbox1
        For iCnt = 0 To Me.listBox2.ListCount - 1
            If Me.listBox2.Selected(iCnt) = True Then
                Me.listBox1.AddItem Me.listBox2.List(iCnt)
            End If
        Next
        
        For iCnt = Me.listBox2.ListCount - 1 To 0 Step -1
            If Me.listBox2.Selected(iCnt) = True Then
                Me.listBox2.RemoveItem iCnt
            End If
        Next
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Transfer from ListBox to ListBox - Revised
    By gsandy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-06-2016, 09:07 PM
  2. Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns
    By sparkoft in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-18-2013, 10:54 AM
  3. Transfer random listbox items to new listbox and then loop through selected items
    By narrowgate88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-31-2013, 05:58 PM
  4. Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox entry.
    By jamieswift1977 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2012, 12:18 PM
  5. Transfer data from Listbox to cell
    By berlini in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2011, 03:57 AM
  6. Transfer Listbox column into Excel Range
    By pjoub06 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-09-2009, 10:07 AM
  7. Replies: 1
    Last Post: 05-17-2006, 04:50 PM

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