VBA To Update Or Refresh Listview Items Or Records Dynamically In Userform

**Excelfun** · 08-12-2014, 03:55 PM

I have userform with datasource in worksheet, now when i add data into worksheet through textbox listview records dont get refreshed,
i have to upload the userform and load again to update the listview.
Is there anyway when i click on add button the data will transfer to worksheet & listview will be automatically updated.

i herewith attach sample file.

**Excel Fox** · 08-12-2014, 08:56 PM

One way to do this is to remove the ListItems as well as the ColumnHeaders, and populate the ListView again.

For your specific case, just replace the first two procedures in your userform module

Code:

'insert records in worksheet
Private Sub CommandButton1_Click()
    Dim r As Long
    Dim m As Long
    Dim rngList As Range
    If Me.TextBox1 = "" Or Me.TextBox2 = "" Or Me.TextBox3 = "" Then
        MsgBox "Please enter All records", vbExclamation
        Exit Sub
    End If
    Set rngList = Worksheets("Sheet1").Range("Listm")
    m = rngList.Rows.Count
    rngList.Cells(m + 1, 2) = Me.TextBox1
    rngList.Cells(m + 1, 3) = Me.TextBox2
    rngList.Cells(m + 1, 4) = Me.TextBox3
    'rngList.Cells(m + 1, 5) = Me.TextBox4
    'Unload Me
Sheet1.Cells(1, 1).Select
Sheet1.Cells(2, 1).Select
LoadListView

End Sub

Private Sub LoadListView()
    'Declare the variables
    Dim wksSource As Worksheet
    Dim rngData As Range
    Dim rngCell As Range
    Dim LstItem As ListItem
    Dim RowCount As Long
    Dim ColCount As Long
    Dim i As Long
    Dim j As Long
    'Set the source worksheet
    Me.ListView1.ListItems.Clear
    Me.ListView1.ColumnHeaders.Clear
    Set wksSource = Worksheets("Sheet1")
        'Set the source range
    Set rngData = wksSource.Range("A1").CurrentRegion
        'Add the column headers
    For Each rngCell In rngData.Rows(1).Cells
        Me.ListView1.ColumnHeaders.Add Text:=rngCell.Value, Width:=90
    Next rngCell
        'Count the number of rows in the source range
    RowCount = rngData.Rows.Count
        'Count the number of columns in the source range
    ColCount = rngData.Columns.Count
        'Fill the ListView
    For i = 2 To RowCount
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
        For j = 2 To ColCount
            LstItem.ListSubItems.Add Text:=rngData(i, j).Value
        Next j
    Next i
    
End Sub