Adding an icon to a listview column header when sorting by that column

[cdchapman]

Hi,

I'm hoping an Excel guru on here can help me with a couple of problems that I've not been able to figure out.

I've designed a spreadsheet for work that uses a listview to display data that is held on a sheet (this is taken from a textfile stored on a network drive). The data is 18000 rows by about 20 columns. The number of rows increases on a daily basis depending as new data is added to the textfile on a daily basis. The number of columns will remain fairly static.

The first problem I have is that when initialising the userform that contains the listview, it takes around 2 or 3 minutes to load the listview with the data from the 18000+ rows. Does anyone know whether there is a quicker way of initially loading the 18000+ rows of data into the listview?

Secondly, I have code that sorts the data shown in the listview when any of the column headers is clicked once. The sort order cycles between no sort -> ascending -> descending. Whilst this code works fine, there is no indication of the current sort order in the column header, so my question is, is there a way of appending some sort of icon to the column header that has been clicked to indicate the sort order. I thought something like a down arrow for ascending, an up arrow for descending and nothing for the no sort.

So, for example if a column named 'Lives' has been sorted in ascending order, the column header title would show 'Lives <down arrow image>', for descending order 'Lives <up arrow image>' and for no sort just 'Lives'.

Apologies for the long explanation above, but I just wanted to be as clear as possible (hopefully)!

Any assistance will be gratefully received as this is beginning to frustrate me!

Chris

 

[Snakehips]

Chris,

Re Secondly.

Assuming that your headers are derived from cell values????
Then if for example 'Lives' is the value of Sheet1 A1 you could add to your sort code something like.....

When ascending.... Sheets("Sheet1").Range("A1").Value = "Lives" & Chrw(9660)

will give text + black triangle apex down.

When descending.... Sheets("Sheet1").Range("A1").Value = "Lives" & Chrw(9650)
will give text + black triangle apex up.

When no sort.... Sheets("Sheet1").Range("A1").Value = "Lives"

If not of direct value, this may give you an idea on how you could use the & Chrw(9650)

Hope that helps.

 

[cdchapman]

Tony,

Many thanks for the help - I never knew the ChrW command existed. I'll try it out at work tomorrow and let you know how it goes....

Chris

 

[cdchapman]

Tony,

Apologies for the delay in getting back to you - it's been busy at work with it being the last week before the Christmas holidays etc....

Have tested this out at work and unfortunately it seems that the listview control does not recognise any characters above chrw(999). In fact it only recognises all characters up to chrw(255) and only various characters from 256 to 999. If I use any character above 999, all I get is a question mark instead of the character. Really strange?

In fact I've just found out today that there is a possibility of the work PC's being upgraded to Windows 7 at some point during next year. From what I've read on this and other forums, the listview control does not work with Windows 7 due to a recent security upgrade. Whether this is true or not, I don't know, but if it is I don't want to carry on using listviews if I will have to revisit this again if we are upgraded to Windows 7.

Do you know of any good alternatives to the listview control that can be used in VBA?

Chris