Solved

Excel form refresh add more columns to ListView

Posted on 2011-03-11
7
536 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:ssmith94015
  • 5
  • 2
7 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35113589
>>> but its add more columns to the ListView to the right.  It does show the chagnes, but I get extra columns.

Clear the listview first :)

Sid
0
 

Author Comment

by:ssmith94015
ID: 35129864
Sid, sorry I have not gotten back, but my son and his wife live in Chiba in Japan and I have spent the last few days trying to find them.  They are safe and I will get to this question.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35130020
Glad to know they are safe. That must have been a huge relief for you!

Please take your time :) Family First :)

Sid
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:ssmith94015
ID: 35130755
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

Open in new window

0
 

Author Comment

by:ssmith94015
ID: 35130980
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!
0
 

Author Closing Comment

by:ssmith94015
ID: 35130995
This worked to clear the items.  It prompted me to look further.
0
 

Author Comment

by:ssmith94015
ID: 35130998
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

Open in new window

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This collection of functions covers all the normal rounding methods of just about any numeric value.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question