Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

Listbox Max Columns

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
steve - 13 Mar 2008 18:34 GMT
EXCEL 2003
I am filling 2 listboxes.  The first listbox is filled like this:

   lr = LastRow(ThisWorkbook.Worksheets("Mesh"))
   With ThisWorkbook.Worksheets("Mesh")
       Set MeshInventory = .Range(.Cells(2, "A"), .Cells(lr, "K"))
   End With
   With Me.lbMeshInventory
       .ColumnHeads = False   'can't use this feature here
       .ColumnCount = MeshInventory.Columns.Count
       .List = MeshInventory.Value
   End With

Since this source is bound, it lest me have more than 10  columns in the
listbox.  When the user double clicks an item on the first Listbox, I want
that item added to the second listbox.  I'm pulling my hair out trying to
figure out how to make the second listbox bound so it can also have more than
10 columns.   Dave Peterson posted something on 3/1/07 and said to use
.AddItem, but I get an error when the code gets to the 10th column.

thank you,
Steve
JLGWhiz - 13 Mar 2008 21:20 GMT
I think you would have to go back to your list source to pick up all eleven
columns.
It will only pick up the bound column value or the first column value by
default as the Value source from one list box to the other.  To get a row of
eleven columns you would need and array or list to load the list box.

> EXCEL 2003
> I am filling 2 listboxes.  The first listbox is filled like this:
[quoted text clipped - 18 lines]
> thank you,
> Steve
Dave Peterson - 13 Mar 2008 21:24 GMT
But you didn't say that you were exceeding 10 columns in that post--I hope I
wouldn't have suggested .additem if you had included that.

If you assign an array to the .list, then you can have more than 10 columns.
Your posted code isn't really using a bound control.

.rowsource = MeshInventory.address(external:=true)

would be bound to the worksheet.

I created a small userform with 2 listboxes and 2 commandbuttons.  I put some
test data in Sheet1 A1:BB10 (just the cell's address)--but way more than 10
columns.

This was behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
   Unload Me
End Sub
Private Sub CommandButton2_Click()
   Dim rCtr As Long
   Dim cCtr As Long
   Dim lCtr As Long
   Dim myArr As Variant
   
   rCtr = -1
   With Me.ListBox1
       'since you can only change the last dimension,
       'this array is transposed
       'columns x rows instead of rows x columns
       ReDim myArr(0 To .ColumnCount - 1, 0 To .ListCount - 1)
       For lCtr = 0 To .ListCount - 1
           If .Selected(lCtr) = True Then
               rCtr = rCtr + 1
               For cCtr = 0 To .ColumnCount - 1
                   myArr(cCtr, rCtr) = .List(lCtr, cCtr)
               Next cCtr
           End If
       Next lCtr
   
       If rCtr = -1 Then
           'nothing selected
           Beep
       Else
           ReDim Preserve myArr(0 To .ColumnCount - 1, 0 To rCtr)
           Me.ListBox2.List = Application.Transpose(myArr)
       End If
       
   End With
End Sub
Private Sub UserForm_Initialize()
   Dim MeshInventory As Range
   
   With ThisWorkbook.Worksheets("Sheet1")
       Set MeshInventory = .Range("A2:bb" & .Cells(.Rows.Count, "K").Row)
   End With
   
   With Me.ListBox1
       .MultiSelect = fmMultiSelectMulti
       .ColumnHeads = False   'can't use this feature here
       .ColumnCount = MeshInventory.Columns.Count
       .List = MeshInventory.Value
   End With
   
   With Me.ListBox2
       .ColumnHeads = False
       .ColumnCount = Me.ListBox1.ColumnCount
   End With
   
   With Me.CommandButton1
       .Caption = "Cancel"
       .Cancel = True
   End With
   
   With Me.CommandButton2
       .Caption = "Transfer to lb2"
       .Default = True
   End With
End Sub

> EXCEL 2003
> I am filling 2 listboxes.  The first listbox is filled like this:
[quoted text clipped - 18 lines]
> thank you,
> Steve

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.