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)



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

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

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

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

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 wrote:

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


--

Dave Peterson

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