Listbox in a form

[Guest]

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.

 

[Andy Pope]

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

 

[Guest]

Thank you very much,
How do I use variant array?

 

[Andy Pope]

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

 

[Guest]

Many Thanks

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