multicolumn listbox

[Peter]

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]

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

 

[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.

 

[Peter]

It was what I needed, Dave. Thank you.

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]

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

 

[Chip Pearson]

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)

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]

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).

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

 

[Chip Pearson]

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)

 

[Peter]

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

Sorry for that.

Thank you for your help.

Gr.,
Peter

 

[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!

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

Sorry for that.

Thank you for your help.

Gr.,
Peter