initial value for listview from excel

[white_flag]

hello
how can an listview be populated with an dynamic range?

[vba]
Private Sub UserForm_Activate()

Dim i As Long
Dim li As ListItem
On Error GoTo Eroare:

With Me.ListView1
.ColumnHeaders.Add , , "Name", Me.TextBox2.Width 'Add columns
.ColumnHeaders.Add , , "Density", Me.TextBox1.Width
.HideColumnHeaders = False 'set some properties
.View = lvwReport
.Gridlines = True

.ListItems = Range("B2:F2").Value - not working
'.ListItems = Range("name").Value - not working


For i = 1 To mcolRecords.Count 'populate listview
Set li = .ListItems.Add(, , mcolRecords(i).Name)
li.SubItems(1) = mcolRecords(i).Density
Next i

ListView1_ItemClick .ListItems(.SelectedItem.Index) 'fill edit controls
End With


Eroare:
'MsgBox "error"

End Sub

[/vba] "name" is: =OFFSET(Sheet1!A3;0;1;1;COUNTA(Sheet1!$1:$1)-1) in excel

thank you.

[ZVI]

Hi White_Flag,
Try this example to populate ListView by test data of A1:C5 range and change the code to suit your task:

Private Sub UserForm_Activate()

  Dim a(), r&, c&, rs&, cs&

  With ListView1

    ' Do some common settings
    .View = lvwReport
    .Gridlines = True
    .HideColumnHeaders = False

    ' Create the column headers
    .ColumnHeaders.Add , , "Name", 50
    .ColumnHeaders.Add , , "Density", 50
    .ColumnHeaders.Add , , "Comment", 50

    ' Populate test range with 3 columns of data
    With Range("A1:C5")
      .Formula = "=COLUMN()& ""-""&ROW()"
      a() = .Value  ' <-- copy test data into array
    End With

    ' Copy test data from a() to ListView1
    rs = UBound(a, 1)
    cs = UBound(a, 2)
    For r = 1 To rs
      With .ListItems.Add(, , a(r, 1))
        For c = 2 To cs
          .SubItems(c - 1) = a(r, c)
        Next
      End With
    Next

  End With

End Sub

Regards,
Vladimir

[white_flag]

thx ZVI for replay. this is the solution that I am using ok it i snot an dynamic range but it is feeting in my case:

[VBA]
Private Sub UserForm_Activate()
Dim LR As Long, LC As Long
With Sheets("Sheet1")
LR = .Range("A" & .Rows.Count).End(xlUp).Row
LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

Dim cell As Range
Dim i As Long
Dim li As ListItem
On Error GoTo Eroare:

With Me.ListView1
.ColumnHeaders.Add , , "Name", Me.TextBox2.Width 'Add columns
'.ColumnHeaders.Add , , "Density", Me.TextBox1.Width
.HideColumnHeaders = False 'set some properties
.View = lvwReport
.Gridlines = True
'Range("A2:G2")
For Each cell In Range("A2", Cells(LR, LC))
.ListItems.Add , , cell
Next cell

ListView1_ItemClick .ListItems(.SelectedItem.Index) 'fill edit controls
End With

Eroare:
'MsgBox "error"
End Sub

[/VBA]