PDA

View Full Version : initial value for listview from excel



white_flag
04-20-2010, 03:02 AM
hello
how can an listview be populated with an dynamic range?


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

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

thank you.

ZVI
04-20-2010, 08:15 AM
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
04-20-2010, 09:09 AM
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:


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