move item from one listbox to another listbox

[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

 

[Bob Phillips]

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)

 

[Dave Peterson]

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

 

[steve]

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

 

[Bob Phillips]

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)

 

[Dave Peterson]

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.

 

[steve]

I get an error message for ".ListFillRange"

steve