ListBox Item Transfer

[MSingh]

Hi,

How can i adapt J Walkenbach's, Excel 2007 Power Programming with VBA, Chapter14- ListBox Item Transfer:

Private Sub AddButton_Click()
Dim i As Integer

If ListBox1.ListIndex = -1 Then Exit Sub
If Not cbDuplicates Then
' See if item already exists
For i = 0 To ListBox2.ListCount - 1
If ListBox1.Value = ListBox2.List(i) Then
Beep
Exit Sub
End If
Next i
End If
ListBox2.AddItem ListBox1.Value
End Sub

such that the AddButton_Click transfers a variable number of columns to ListBox2.
The Variable number of columns=value that user types into TexBox1.


Does RemoveButton_Click() also need adapting?

Private Sub RemoveButton_Click()
If ListBox2.ListIndex = -1 Then Exit Sub
ListBox2.RemoveItem ListBox2.ListIndex
End Sub


Thanks again
Mohamed

[HansV]

Add the following declaration at the beginning:

Code: Select all

  Dim n As Integer

and add the following code below the line with AddItem, above End Sub:

Code: Select all

  ListBox2.AddItem ListBox1
  n = ListBox2.ListCount - 1
  For i = 1 To ListBox1.ColumnCount - 1
    ListBox2.Column(i, n) = ListBox1.Column(i)
  Next i

You don't have to change the code for removing an item - RemoveItem deletes an entire row.

[MSingh]

Hi Hans,

Thanks yet again.
Works perfectly!

Kind Regards
Mohamed

[MSingh]

Hi,

These are follow-on questions. It relates to the previous post which code you provided & which works perfectly
(ie.ListBox1 is multi-column, variable number of columns)

I decided to rather ask 3 questions simultaneously:

Questions 1 & 2 again adapting J Walkenbach's code:

1. After having transferred data from Listbox1 to Listbox 2, how do move a row of data up in Listbox2 (not just the first column)?

Private Sub MoveUpButton_Click()
Dim NumItems As Integer
Dim i As Integer
Dim ItemNum As Integer
Dim TempItem As String
Dim TempList()

If ListBox2.ListIndex = 0 Then Exit Sub

NumItems = ListBox2.ListCount
ReDim TempList(0 To NumItems - 1)

' Fill array with list box items
For i = 0 To NumItems - 1
TempList(i) = ListBox2.List(i)
Next i

' Selected item
ItemNum = ListBox2.ListIndex

' Exchange items
TempItem = TempList(ItemNum)
TempList(ItemNum) = TempList(ItemNum - 1)
TempList(ItemNum - 1) = TempItem
ListBox2.List = TempList

' Change the list index
ListBox2.ListIndex = ItemNum - 1
End Sub


2. How do I move the entire row down in ListBox2?

Private Sub MoveDownButton_Click()
Dim NumItems As Integer
Dim i As Integer
Dim ItemNum As Integer
Dim TempItem As String
Dim TempList()

If ListBox2.ListIndex = ListBox2.ListCount - 1 Then Exit Sub

NumItems = ListBox2.ListCount
ReDim TempList(0 To NumItems - 1)

' Fill array with list box items
For i = 0 To NumItems - 1
TempList(i) = ListBox2.List(i)
Next i

' Selected item
ItemNum = ListBox2.ListIndex

' Exchange items
TempItem = TempList(ItemNum)
TempList(ItemNum) = TempList(ItemNum + 1)
TempList(ItemNum + 1) = TempItem
ListBox2.List = TempList

' Change the list index
ListBox2.ListIndex = ItemNum + 1
End Sub

3. How can I move all data from multi-column ListBox1 to ListBox2?

Code: Select all

Private Sub btnSelectAll_Click()
' moves all items from the  listbox1 to listbox2
Dim i As Integer
    With Me.ListBox1
        If .ListCount > 0 Then
            Application.ScreenUpdating = False
            For i = 0 To .ListCount - 1
                Me.ListBox2.AddItem .List(i)
            Next i
        End If
    End With
End Sub

Thanking you sincerely for answering so many questions, i lost count.
Mohamed

[rory]

3.

Code: Select all

Listbox2.List = Listbox1.List

[HansV]

Question 1:

Code: Select all

Private Sub MoveUpButton_Click()
  Dim NumItems As Integer
  Dim i As Integer
  Dim ItemNum As Integer
  Dim TempItem As String
  Dim TempList()

  If ListBox2.ListIndex <= 0 Then Exit Sub

  ' Get list
  TempList = ListBox2.List

  ' Selected item
  ItemNum = ListBox2.ListIndex

  ' Exchange items
  For i = 0 To ListBox2.ColumnCount - 1
    TempItem = TempList(ItemNum, i)
    TempList(ItemNum, i) = TempList(ItemNum - 1, i)
    TempList(ItemNum - 1, i) = TempItem
  Next i
  
  ' Set list
  ListBox2.List = TempList

  ' Change the list index
  ListBox2.List = TempList
End Sub

and similar for Question 2.

[MSingh]

Thank you Hans & Rory. Code worked perfectly.