Sandra Smith
asked on
Excel form refresh add more columns to ListView
I have an Excel form that uses a ListView to present data. The user clicks on one of the rows and the row information appears in editing boxes. However, when the user changes information, I want the form to refresh with the new data, that list, the listview reflect the changed information. However, I tried calling the form's initallize event to refresh the ListView, but its add more columns to the ListView to the right. It does show the chagnes, but I get extra columns. That is, the orignal data columns appear, but another set of blank columns are added at the end. I tried using Me.Repaint, but that does not refresh th form to reflect the new data. So,how do I get the ListView to refresh its data after the user has made and saved the changes?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to know they are safe. That must have been a huge relief for you!
Please take your time :) Family First :)
Sid
Please take your time :) Family First :)
Sid
ASKER
I have the clear method in the Form's initalize event, but that does not seem to prevent the extra columns when it is called again to refresh after a change. I have attached the code. This is actually inits own module that is called when the form is initialized so I also call it when I delete data.
'Need to figure out how to get rid of SalesLogic control at start of procedure and then
'add it back at the end to the reference library.
Dim lvItem As ListItem
Dim i As Integer
flgEdit = "No"
flgAdd = "No"
intRowStart = fntRangeStartEndsRows("Start", "ss_ACTQualitybreakdown")
intRowEnd = fntRangeStartEndsRows("End", "ss_ACTQualitybreakdown")
'Now initialize the listview box
ListView1.ColumnHeaders.Add , , "Quality Breakdown", 90
ListView1.ColumnHeaders.Add , , "LookUp Value", 75
ListView1.ColumnHeaders.Add , , "", 0
ListView1.ColumnHeaders.Add , , "", 0
ListView1.ColumnHeaders.Add , , "", 0
ListView1.ColumnHeaders.Add , , "Sort Order", 50
ListView1.View = lvwReport
ListView1.FullRowSelect = True
ListView1.ListItems.Clear
For i = intRowStart To intRowEnd
Set lvItem = ListView1.ListItems.Add()
lvItem.text = Worksheets("DataSource").Range("A" & i).Value
ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Worksheets("DataSource").Range("B" & i).Value)
ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Worksheets("DataSource").Range("C" & i).Value)
ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Worksheets("DataSource").Range("D" & i).Value)
ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Worksheets("DataSource").Range("E" & i).Value)
ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Worksheets("DataSource").Range("F" & i).Value)
Next i
ASKER
Sid, I figured it out! When I call the InitializeListView1 event in the Delete code, it is doing exactly what I told it to do! it is adding another set of column headers as it goes thorough the code. I moved the code to a another module, called it ReinitalizeListView1, but took out the ColumnHeaders adding section. Now, it does exactly what I want, refreshes the form with the correct data, but no extra columns are appended!
ASKER
This worked to clear the items. It prompted me to look further.
ASKER
Attached is the final version of what does work.
Private Sub ReInitializeListView1()
Dim lvItem As ListItem
Dim i As Integer
flgEdit = "No"
flgAdd = "No"
intRowStart = fntRangeStartEndsRows("Start", "ss_ACTQualitybreakdown")
intRowEnd = fntRangeStartEndsRows("End", "ss_ACTQualitybreakdown")
ListView1.View = lvwReport
ListView1.FullRowSelect = True
ListView1.ListItems.Clear
For i = intRowStart To intRowEnd
Set lvItem = ListView1.ListItems.Add()
lvItem.text = Worksheets("DataSource").Range("A" & i).Value
ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Worksheets("DataSource").Range("B" & i).Value)
ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Worksheets("DataSource").Range("C" & i).Value)
ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Worksheets("DataSource").Range("D" & i).Value)
ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Worksheets("DataSource").Range("E" & i).Value)
ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Worksheets("DataSource").Range("F" & i).Value)
Next i
End Sub
ASKER