Hi there,

After searching and searching and not finding, I've decided it's time to
post my own question.

I have a userform with two listboxes. The first one, I've managed to make
into a multicolumn listbox and to fill it with the contents of an excel 2
column range. I've also been able to get code from the net to "copy"an item
form Listbox1 to Listbox2, except for this: Only the part in the bound
column gets copied to Listbox2. BTW, listbox nr is also marked as a
multicolumn listbox.

How do I get the procedure to copy both parts to Listbox2, or is it simply a
matter of setting the correct properties for Listbox2 ?

I work with Office 2007, Vista Home Prof (Just in case you were wondering).

Greetings,
Peter

This may get you started:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
Me.ListBox2.AddItem .List(iCtr, 0)
Me.ListBox2.List(Me.ListBox2.ListCount - 1, 1) = .List(iCtr, 1)
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ColumnCount = 2
.List = Worksheets("sheet1").Range("a1:b20").Value
End With
With Me.ListBox2
.MultiSelect = fmMultiSelectMulti
.ColumnCount = 2
End With
Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Copy to ListBox2"
End Sub

Peter wrote:

Hi there,

After searching and searching and not finding, I've decided it's time to
post my own question.

I have a userform with two listboxes. The first one, I've managed to make
into a multicolumn listbox and to fill it with the contents of an excel 2
column range. I've also been able to get code from the net to "copy"an item
form Listbox1 to Listbox2, except for this: Only the part in the bound
column gets copied to Listbox2. BTW, listbox nr is also marked as a
multicolumn listbox.

How do I get the procedure to copy both parts to Listbox2, or is it simply a
matter of setting the correct properties for Listbox2 ?

I work with Office 2007, Vista Home Prof (Just in case you were wondering).

Greetings,
Peter

--

Dave Peterson

I like this better.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
Dim lCtr As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
Me.ListBox2.AddItem .List(iCtr, 0)
For lCtr = 1 To .ColumnCount - 1
Me.ListBox2.List(Me.ListBox2.ListCount - 1, lCtr) _
= .List(iCtr, 1)
Next lCtr
End If
Next iCtr
End With

End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("Sheet1")
Set myRng = .Range("a1:b20")
End With
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ColumnCount = myRng.Columns.Count
.List = myRng.Value
End With
With Me.ListBox2
.MultiSelect = fmMultiSelectMulti
.ColumnCount = Me.ListBox1.ColumnCount
End With
Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Copy to ListBox2"

End Sub

It's about the same, but relies less on hardcoded values.


Dave Peterson wrote:

<<snipped
Peter wrote:

Hi there,

After searching and searching and not finding, I've decided it's time to
post my own question.

I have a userform with two listboxes. The first one, I've managed to make
into a multicolumn listbox and to fill it with the contents of an excel 2
column range. I've also been able to get code from the net to "copy"an item
form Listbox1 to Listbox2, except for this: Only the part in the bound
column gets copied to Listbox2. BTW, listbox nr is also marked as a
multicolumn listbox.

How do I get the procedure to copy both parts to Listbox2, or is it simply a
matter of setting the correct properties for Listbox2 ?

I work with Office 2007, Vista Home Prof (Just in case you were wondering).

Greetings,
Peter

--

Dave Peterson

--

Dave Peterson

It was what I needed, Dave. Thank you.

Peter

"Dave Peterson" schreef in bericht
...
This may get you started:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
Me.ListBox2.AddItem .List(iCtr, 0)
Me.ListBox2.List(Me.ListBox2.ListCount - 1, 1) =
.List(iCtr, 1)
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ColumnCount = 2
.List = Worksheets("sheet1").Range("a1:b20").Value
End With
With Me.ListBox2
.MultiSelect = fmMultiSelectMulti
.ColumnCount = 2
End With
Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Copy to ListBox2"
End Sub

Peter wrote:

Hi there,

After searching and searching and not finding, I've decided it's time to
post my own question.

I have a userform with two listboxes. The first one, I've managed to make
into a multicolumn listbox and to fill it with the contents of an excel 2
column range. I've also been able to get code from the net to "copy"an
item
form Listbox1 to Listbox2, except for this: Only the part in the bound
column gets copied to Listbox2. BTW, listbox nr is also marked as a
multicolumn listbox.

How do I get the procedure to copy both parts to Listbox2, or is it
simply a
matter of setting the correct properties for Listbox2 ?

I work with Office 2007, Vista Home Prof (Just in case you were
wondering).

Greetings,
Peter

--

Dave Peterson

Hi Dave,

I've taken a closer look at the adjusted code. I get what it is you're
making clear: make the code more generic so that it is applicable when other
circumstances are applicable. Then I stumbled upon on bit of the adjusted
code, which is this line:

For lCtr = 1 To .ColumnCount - 1

If I understand this vba-thing correctly, that means that this particular
"For ... Next"-loop does not handle all of the columns assigned to the
listbox, but all minus 1. If the column-number-assignment of column-items in
a listbox was handled the same way the pages of a multipage-object are
numbered, I´d expect to find "For lCtr = 0 To .ColumnCount - 1" in which
".ColumnCount" would represent the number of colums in the listbox and the
looping-proces would then end with number n-1. I noticed that column numbers
of listbox-items are handled in the default manner of "1 to n" in contrast
to the ".Add Item"-method, whicht assigns item numbers starting with number
0, just like the MultiPage object does.

My question therefore is: Am I right in my assumptions mentioned above and
"For lCtr = 1 To .ColumnCount - 1" should be "For lCtr = 1 To .ColumnCount",
without the "-1" part, or am I really way off base here?

Greetings,
Peter


"Dave Peterson" schreef in bericht
...
I like this better.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
Dim lCtr As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
Me.ListBox2.AddItem .List(iCtr, 0)
For lCtr = 1 To .ColumnCount - 1
Me.ListBox2.List(Me.ListBox2.ListCount - 1, lCtr) _
= .List(iCtr, 1)
Next lCtr
End If
Next iCtr
End With

End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("Sheet1")
Set myRng = .Range("a1:b20")
End With
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ColumnCount = myRng.Columns.Count
.List = myRng.Value
End With
With Me.ListBox2
.MultiSelect = fmMultiSelectMulti
.ColumnCount = Me.ListBox1.ColumnCount
End With
Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Copy to ListBox2"

End Sub

It's about the same, but relies less on hardcoded values.


Dave Peterson wrote:

<<snipped
Peter wrote:

Hi there,

After searching and searching and not finding, I've decided it's time
to
post my own question.

I have a userform with two listboxes. The first one, I've managed to
make
into a multicolumn listbox and to fill it with the contents of an excel
2
column range. I've also been able to get code from the net to "copy"an
item
form Listbox1 to Listbox2, except for this: Only the part in the bound
column gets copied to Listbox2. BTW, listbox nr is also marked as a
multicolumn listbox.

How do I get the procedure to copy both parts to Listbox2, or is it
simply a
matter of setting the correct properties for Listbox2 ?

I work with Office 2007, Vista Home Prof (Just in case you were
wondering).

Greetings,
Peter

--

Dave Peterson

--

Dave Peterson

I believe the post you cite is in error. To get all columns, use

For lCtr = 0 To .ColumnCount -1

Indices into the List object are 0-based.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Peter" wrote in message
l.nl...
Hi Dave,

I've taken a closer look at the adjusted code. I get what it is you're
making clear: make the code more generic so that it is applicable when
other circumstances are applicable. Then I stumbled upon on bit of the
adjusted code, which is this line:

For lCtr = 1 To .ColumnCount - 1

If I understand this vba-thing correctly, that means that this particular
"For ... Next"-loop does not handle all of the columns assigned to the
listbox, but all minus 1. If the column-number-assignment of column-items
in a listbox was handled the same way the pages of a multipage-object are
numbered, I´d expect to find "For lCtr = 0 To .ColumnCount - 1" in which
".ColumnCount" would represent the number of colums in the listbox and the
looping-proces would then end with number n-1. I noticed that column
numbers of listbox-items are handled in the default manner of "1 to n" in
contrast to the ".Add Item"-method, whicht assigns item numbers starting
with number 0, just like the MultiPage object does.

My question therefore is: Am I right in my assumptions mentioned above and
"For lCtr = 1 To .ColumnCount - 1" should be "For lCtr = 1 To
.ColumnCount", without the "-1" part, or am I really way off base here?

Greetings,
Peter


"Dave Peterson" schreef in bericht
...
I like this better.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
Dim lCtr As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
Me.ListBox2.AddItem .List(iCtr, 0)
For lCtr = 1 To .ColumnCount - 1
Me.ListBox2.List(Me.ListBox2.ListCount - 1, lCtr) _
= .List(iCtr, 1)
Next lCtr
End If
Next iCtr
End With

End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("Sheet1")
Set myRng = .Range("a1:b20")
End With
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ColumnCount = myRng.Columns.Count
.List = myRng.Value
End With
With Me.ListBox2
.MultiSelect = fmMultiSelectMulti
.ColumnCount = Me.ListBox1.ColumnCount
End With
Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Copy to ListBox2"

End Sub

It's about the same, but relies less on hardcoded values.


Dave Peterson wrote:

<<snipped
Peter wrote:

Hi there,

After searching and searching and not finding, I've decided it's time
to
post my own question.

I have a userform with two listboxes. The first one, I've managed to
make
into a multicolumn listbox and to fill it with the contents of an
excel 2
column range. I've also been able to get code from the net to "copy"an
item
form Listbox1 to Listbox2, except for this: Only the part in the bound
column gets copied to Listbox2. BTW, listbox nr is also marked as a
multicolumn listbox.

How do I get the procedure to copy both parts to Listbox2, or is it
simply a
matter of setting the correct properties for Listbox2 ?

I work with Office 2007, Vista Home Prof (Just in case you were
wondering).

Greetings,
Peter

--

Dave Peterson

--

Dave Peterson

I'm not sure if you (and Chip) saw that I added the item first with .additem.
Then I got the rest of the columns (1 to .columncount - 1)

If .Selected(iCtr) Then
'add a new item to the second listbox
Me.ListBox2.AddItem .List(iCtr, 0)
'add the rest of the columns to the second listbox
For lCtr = 1 To .ColumnCount - 1
Me.ListBox2.List(Me.ListBox2.ListCount - 1, lCtr) _
= .List(iCtr, 1)
Next lCtr
End If

If I were just picking off the columns in the listbox--say to populate a range
of cells, then I would have gone from 0 to .columncount -1 (just like Chip
suggested).

But I needed to "prime the pump" with that first column--and I didn't want to
grab it again in the loop--so the loop started at 1 (not 0).







Peter wrote:

Hi Dave,

I've taken a closer look at the adjusted code. I get what it is you're
making clear: make the code more generic so that it is applicable when other
circumstances are applicable. Then I stumbled upon on bit of the adjusted
code, which is this line:

For lCtr = 1 To .ColumnCount - 1

If I understand this vba-thing correctly, that means that this particular
"For ... Next"-loop does not handle all of the columns assigned to the
listbox, but all minus 1. If the column-number-assignment of column-items in
a listbox was handled the same way the pages of a multipage-object are
numbered, I´d expect to find "For lCtr = 0 To .ColumnCount - 1" in which
".ColumnCount" would represent the number of colums in the listbox and the
looping-proces would then end with number n-1. I noticed that column numbers
of listbox-items are handled in the default manner of "1 to n" in contrast
to the ".Add Item"-method, whicht assigns item numbers starting with number
0, just like the MultiPage object does.

My question therefore is: Am I right in my assumptions mentioned above and
"For lCtr = 1 To .ColumnCount - 1" should be "For lCtr = 1 To .ColumnCount",
without the "-1" part, or am I really way off base here?

Greetings,
Peter

"Dave Peterson" schreef in bericht
...
I like this better.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
Dim lCtr As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
Me.ListBox2.AddItem .List(iCtr, 0)
For lCtr = 1 To .ColumnCount - 1
Me.ListBox2.List(Me.ListBox2.ListCount - 1, lCtr) _
= .List(iCtr, 1)
Next lCtr
End If
Next iCtr
End With

End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("Sheet1")
Set myRng = .Range("a1:b20")
End With
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ColumnCount = myRng.Columns.Count
.List = myRng.Value
End With
With Me.ListBox2
.MultiSelect = fmMultiSelectMulti
.ColumnCount = Me.ListBox1.ColumnCount
End With
Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Copy to ListBox2"

End Sub

It's about the same, but relies less on hardcoded values.


Dave Peterson wrote:

<<snipped
Peter wrote:

Hi there,

After searching and searching and not finding, I've decided it's time
to
post my own question.

I have a userform with two listboxes. The first one, I've managed to
make
into a multicolumn listbox and to fill it with the contents of an excel
2
column range. I've also been able to get code from the net to "copy"an
item
form Listbox1 to Listbox2, except for this: Only the part in the bound
column gets copied to Listbox2. BTW, listbox nr is also marked as a
multicolumn listbox.

How do I get the procedure to copy both parts to Listbox2, or is it
simply a
matter of setting the correct properties for Listbox2 ?

I work with Office 2007, Vista Home Prof (Just in case you were
wondering).

Greetings,
Peter

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

I'm not sure if you (and Chip) saw that I added the item first with
.additem.

Nope, I didn't read the whole thread. Shame on me.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Dave Peterson" wrote in message
...
I'm not sure if you (and Chip) saw that I added the item first with
.additem.
Then I got the rest of the columns (1 to .columncount - 1)

If .Selected(iCtr) Then
'add a new item to the second listbox
Me.ListBox2.AddItem .List(iCtr, 0)
'add the rest of the columns to the second listbox
For lCtr = 1 To .ColumnCount - 1
Me.ListBox2.List(Me.ListBox2.ListCount - 1, lCtr) _
= .List(iCtr, 1)
Next lCtr
End If

If I were just picking off the columns in the listbox--say to populate a
range
of cells, then I would have gone from 0 to .columncount -1 (just like Chip
suggested).

But I needed to "prime the pump" with that first column--and I didn't want
to
grab it again in the loop--so the loop started at 1 (not 0).







Peter wrote:

Hi Dave,

I've taken a closer look at the adjusted code. I get what it is you're
making clear: make the code more generic so that it is applicable when
other
circumstances are applicable. Then I stumbled upon on bit of the adjusted
code, which is this line:

For lCtr = 1 To .ColumnCount - 1

If I understand this vba-thing correctly, that means that this particular
"For ... Next"-loop does not handle all of the columns assigned to the
listbox, but all minus 1. If the column-number-assignment of column-items
in
a listbox was handled the same way the pages of a multipage-object are
numbered, I´d expect to find "For lCtr = 0 To .ColumnCount - 1" in which
".ColumnCount" would represent the number of colums in the listbox and
the
looping-proces would then end with number n-1. I noticed that column
numbers
of listbox-items are handled in the default manner of "1 to n" in
contrast
to the ".Add Item"-method, whicht assigns item numbers starting with
number
0, just like the MultiPage object does.

My question therefore is: Am I right in my assumptions mentioned above
and
"For lCtr = 1 To .ColumnCount - 1" should be "For lCtr = 1 To
.ColumnCount",
without the "-1" part, or am I really way off base here?

Greetings,
Peter

"Dave Peterson" schreef in bericht
...
I like this better.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
Dim lCtr As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
Me.ListBox2.AddItem .List(iCtr, 0)
For lCtr = 1 To .ColumnCount - 1
Me.ListBox2.List(Me.ListBox2.ListCount - 1, lCtr) _
= .List(iCtr, 1)
Next lCtr
End If
Next iCtr
End With

End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("Sheet1")
Set myRng = .Range("a1:b20")
End With
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ColumnCount = myRng.Columns.Count
.List = myRng.Value
End With
With Me.ListBox2
.MultiSelect = fmMultiSelectMulti
.ColumnCount = Me.ListBox1.ColumnCount
End With
Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Copy to ListBox2"

End Sub

It's about the same, but relies less on hardcoded values.


Dave Peterson wrote:

<<snipped
Peter wrote:

Hi there,

After searching and searching and not finding, I've decided it's
time
to
post my own question.

I have a userform with two listboxes. The first one, I've managed to
make
into a multicolumn listbox and to fill it with the contents of an
excel
2
column range. I've also been able to get code from the net to
"copy"an
item
form Listbox1 to Listbox2, except for this: Only the part in the
bound
column gets copied to Listbox2. BTW, listbox nr is also marked as a
multicolumn listbox.

How do I get the procedure to copy both parts to Listbox2, or is it
simply a
matter of setting the correct properties for Listbox2 ?

I work with Office 2007, Vista Home Prof (Just in case you were
wondering).

Greetings,
Peter

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

I did read the .Additem part, but it didn't sink in.

Sorry for that.

Thank you for your help.

Gr.,
Peter



"Dave Peterson" schreef in bericht
...
I'm not sure if you (and Chip) saw that I added the item first with
.additem.
Then I got the rest of the columns (1 to .columncount - 1)

If .Selected(iCtr) Then
'add a new item to the second listbox
Me.ListBox2.AddItem .List(iCtr, 0)
'add the rest of the columns to the second listbox
For lCtr = 1 To .ColumnCount - 1
Me.ListBox2.List(Me.ListBox2.ListCount - 1, lCtr) _
= .List(iCtr, 1)
Next lCtr
End If

If I were just picking off the columns in the listbox--say to populate a
range
of cells, then I would have gone from 0 to .columncount -1 (just like Chip
suggested).

But I needed to "prime the pump" with that first column--and I didn't want
to
grab it again in the loop--so the loop started at 1 (not 0).







Peter wrote:

Hi Dave,

I've taken a closer look at the adjusted code. I get what it is you're
making clear: make the code more generic so that it is applicable when
other
circumstances are applicable. Then I stumbled upon on bit of the adjusted
code, which is this line:

For lCtr = 1 To .ColumnCount - 1

If I understand this vba-thing correctly, that means that this particular
"For ... Next"-loop does not handle all of the columns assigned to the
listbox, but all minus 1. If the column-number-assignment of column-items
in
a listbox was handled the same way the pages of a multipage-object are
numbered, I´d expect to find "For lCtr = 0 To .ColumnCount - 1" in which
".ColumnCount" would represent the number of colums in the listbox and
the
looping-proces would then end with number n-1. I noticed that column
numbers
of listbox-items are handled in the default manner of "1 to n" in
contrast
to the ".Add Item"-method, whicht assigns item numbers starting with
number
0, just like the MultiPage object does.

My question therefore is: Am I right in my assumptions mentioned above
and
"For lCtr = 1 To .ColumnCount - 1" should be "For lCtr = 1 To
.ColumnCount",
without the "-1" part, or am I really way off base here?

Greetings,
Peter

"Dave Peterson" schreef in bericht
...
I like this better.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
Dim lCtr As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
Me.ListBox2.AddItem .List(iCtr, 0)
For lCtr = 1 To .ColumnCount - 1
Me.ListBox2.List(Me.ListBox2.ListCount - 1, lCtr) _
= .List(iCtr, 1)
Next lCtr
End If
Next iCtr
End With

End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("Sheet1")
Set myRng = .Range("a1:b20")
End With
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ColumnCount = myRng.Columns.Count
.List = myRng.Value
End With
With Me.ListBox2
.MultiSelect = fmMultiSelectMulti
.ColumnCount = Me.ListBox1.ColumnCount
End With
Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Copy to ListBox2"

End Sub

It's about the same, but relies less on hardcoded values.


Dave Peterson wrote:

<<snipped
Peter wrote:

Hi there,

After searching and searching and not finding, I've decided it's
time
to
post my own question.

I have a userform with two listboxes. The first one, I've managed to
make
into a multicolumn listbox and to fill it with the contents of an
excel
2
column range. I've also been able to get code from the net to
"copy"an
item
form Listbox1 to Listbox2, except for this: Only the part in the
bound
column gets copied to Listbox2. BTW, listbox nr is also marked as a
multicolumn listbox.

How do I get the procedure to copy both parts to Listbox2, or is it
simply a
matter of setting the correct properties for Listbox2 ?

I work with Office 2007, Vista Home Prof (Just in case you were
wondering).

Greetings,
Peter

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

No problem. I can't complain when my entire posts aren't read. I'd have to
spend too much time apologizing to others for the same thing!

Peter wrote:

I did read the .Additem part, but it didn't sink in.

Sorry for that.

Thank you for your help.

Gr.,
Peter

"Dave Peterson" schreef in bericht
...
I'm not sure if you (and Chip) saw that I added the item first with
.additem.
Then I got the rest of the columns (1 to .columncount - 1)

If .Selected(iCtr) Then
'add a new item to the second listbox
Me.ListBox2.AddItem .List(iCtr, 0)
'add the rest of the columns to the second listbox
For lCtr = 1 To .ColumnCount - 1
Me.ListBox2.List(Me.ListBox2.ListCount - 1, lCtr) _
= .List(iCtr, 1)
Next lCtr
End If

If I were just picking off the columns in the listbox--say to populate a
range
of cells, then I would have gone from 0 to .columncount -1 (just like Chip
suggested).

But I needed to "prime the pump" with that first column--and I didn't want
to
grab it again in the loop--so the loop started at 1 (not 0).







Peter wrote:

Hi Dave,

I've taken a closer look at the adjusted code. I get what it is you're
making clear: make the code more generic so that it is applicable when
other
circumstances are applicable. Then I stumbled upon on bit of the adjusted
code, which is this line:

For lCtr = 1 To .ColumnCount - 1

If I understand this vba-thing correctly, that means that this particular
"For ... Next"-loop does not handle all of the columns assigned to the
listbox, but all minus 1. If the column-number-assignment of column-items
in
a listbox was handled the same way the pages of a multipage-object are
numbered, I´d expect to find "For lCtr = 0 To .ColumnCount - 1" in which
".ColumnCount" would represent the number of colums in the listbox and
the
looping-proces would then end with number n-1. I noticed that column
numbers
of listbox-items are handled in the default manner of "1 to n" in
contrast
to the ".Add Item"-method, whicht assigns item numbers starting with
number
0, just like the MultiPage object does.

My question therefore is: Am I right in my assumptions mentioned above
and
"For lCtr = 1 To .ColumnCount - 1" should be "For lCtr = 1 To
.ColumnCount",
without the "-1" part, or am I really way off base here?

Greetings,
Peter

"Dave Peterson" schreef in bericht
...
I like this better.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
Dim lCtr As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
Me.ListBox2.AddItem .List(iCtr, 0)
For lCtr = 1 To .ColumnCount - 1
Me.ListBox2.List(Me.ListBox2.ListCount - 1, lCtr) _
= .List(iCtr, 1)
Next lCtr
End If
Next iCtr
End With

End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("Sheet1")
Set myRng = .Range("a1:b20")
End With
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ColumnCount = myRng.Columns.Count
.List = myRng.Value
End With
With Me.ListBox2
.MultiSelect = fmMultiSelectMulti
.ColumnCount = Me.ListBox1.ColumnCount
End With
Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Copy to ListBox2"

End Sub

It's about the same, but relies less on hardcoded values.


Dave Peterson wrote:

<<snipped
Peter wrote:

Hi there,

After searching and searching and not finding, I've decided it's
time
to
post my own question.

I have a userform with two listboxes. The first one, I've managed to
make
into a multicolumn listbox and to fill it with the contents of an
excel
2
column range. I've also been able to get code from the net to
"copy"an
item
form Listbox1 to Listbox2, except for this: Only the part in the
bound
column gets copied to Listbox2. BTW, listbox nr is also marked as a
multicolumn listbox.

How do I get the procedure to copy both parts to Listbox2, or is it
simply a
matter of setting the correct properties for Listbox2 ?

I work with Office 2007, Vista Home Prof (Just in case you were
wondering).

Greetings,
Peter

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson