move item from one listbox to another listbox

  • Thread starter Thread starter steve
  • Start date Start date
S

steve

Listbox1 is populated from a worksheet range, and has Column headers. Right
now I can move an item from Listbox1 to Listbox2 for a single column listbox.
How can I do this for multicolumn listboxes?

Private Sub Listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If Listbox1.ListIndex = -1 Then Exit Sub
Listbox2.AddItem Listbox1.value
End If
End Sub

Also, is there anyway for Listbox2 to have Column headers?


thanks,
Steve
 
Private Sub Listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If ListBox1.ListIndex = -1 Then Exit Sub
ListBox2.AddItem ListBox1.Value
ListBox2.List(ListBox2.ListCount - 1, 1) =
ListBox1.List(ListBox1.ListIndex, 1)

End Sub

as to columnheads, no. Columne heads are pulled from the row above the bound
data, the second isn't bound.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Headers come from worksheet ranges. So if you use .additem, you can't have
headers.

Option Explicit
Private Sub Listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ListBox1.ListIndex = -1 Then Exit Sub

With Me.ListBox2
'.AddItem ListBox1.Value
'or
.AddItem Me.ListBox1.List(Me.ListBox1.ListIndex)
.List(.ListCount - 1, 1) = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
End With

End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = 2
.RowSource = Worksheets("sheet1").Range("a2:b4").Address(external:=True)
.ColumnHeads = True
End With

With Me.ListBox2
.ColumnCount = Me.ListBox1.ColumnCount
.Clear
End With
End Sub
 
Thank you for both of your replies.

The AddItem method is putting the values from Listbox1 into Listbox2, but it
loses the format. If my second column is a date, then it just gives me the
serial number. How do you get around this?

Is there a workaround that you can suggest? Like putting Listbox1 into a
temporary range on a worksheet, then filling Listbox2 so it can have headers?

thanks,
Steve
 
Try this

Private Sub Listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Const TARGET_COL As String = "AA"
Dim LastRow As Long
Dim FirstCol As Long
Dim NumCols As Long
Dim FirstRow As Long

With Me

If .ListBox1.ListIndex = -1 Then Exit Sub

FirstRow = .Range(.ListBox1.ListFillRange).Row - 1
FirstCol = .Range(.ListBox1.ListFillRange).Cells(1, 1).Column
NumCols = .Range(.ListBox1.ListFillRange).Columns.Count

.Cells(FirstRow, FirstCol).Resize(, NumCols).Copy .Cells(FirstRow,
TARGET_COL)

LastRow = .Cells(.Rows.Count, TARGET_COL).End(xlUp).Row
With .ListBox1

Me.Range(.ListFillRange).Cells(1,
1).Offset(.ListIndex).Resize(1, NumCols).Copy Me.Cells(LastRow + 1, "AA")
Me.ListBox2.ListFillRange = Me.Cells(1,
TARGET_COL).Resize(LastRow + 1, NumCols).Address
End With
End With
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
You could also use:

..List(.ListCount - 1, 1) _
= format(Me.ListBox1.List(Me.ListBox1.ListIndex, 1), "mm/dd/yyyy")

And alternative to the headers may be to add a couple of labels above the
listbox that describ each of the fields.
 
Back
Top