|
Posted to microsoft.public.excel.programming
|
|
|
Listbox in a form
Many Thanks
"Andy Pope" wrote:
Hi,
Firstly a correction. With additems you get 10 columns, as you stated.
On a blank worksheet put this formula in A1
A1: =ADDRESS(ROW(),COLUMN())
Fill the formula across range A1:IV10
Now create a userform with 3 listboxes and 1 commandbutton.
Paste the following code.
Private Sub CommandButton1_Click()
Dim lngCol As Long
Dim lngRow As Long
Dim vntData As Variant
With Range("A1:IV10")
' 256 columns
ListBox1.ColumnCount = .Columns.Count
ListBox1.RowSource = .Address
' 256 columns form variant array
vntData = .Value
ListBox2.ColumnCount = .Columns.Count
ListBox2.List = vntData
' additem approach
On Error GoTo ErrAddColumn
For lngCol = 1 To .Columns.Count
ListBox3.ColumnCount = lngCol
For lngRow = 1 To .Rows.Count
If lngCol = 1 Then
ListBox3.AddItem .Cells(lngRow, lngCol)
Else
ListBox3.List(lngRow - 1, lngCol - 1) = _
Cells(lngRow, lngCol)
End If
Next
Next
End With
ErrAddColumn:
Exit Sub
End Sub
Cheers
Andy
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Dan" wrote in message
...
Thank you very much,
How do I use variant array?
"Andy Pope" wrote:
Hi,
It depends how you are populating the listbox.
Using additem the limit is 9
Using RowSource 256
Using variant array unlimited
The draw back of the Rowsource is you can not change the listbox entry
directly. Instead you change the linked cell.
Cheers
Andy
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Dan" wrote in message
...
Is there a restriction on how many colums you can display.
I manage to display only up to 10, even after changing the BoundColumn
and
BoundCount property to 17.
|