I get an error message for ".ListFillRange"

steve

"Bob Phillips" wrote:

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)



"steve" wrote in message
...
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



"Dave Peterson" wrote:

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(extern al:=True)
.ColumnHeads = True
End With

With Me.ListBox2
.ColumnCount = Me.ListBox1.ColumnCount
.Clear
End With
End Sub


steve wrote:

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

--

Dave Peterson