I've been looking for an answer to this for some time and I can't find anything in the books or on line to help... perhaps someone out there has the answer...

How can I read, as a variable, the distance (preferably in points) that a multicolumn userform is scrolled horizontally?

The problem is this...

I have a large multipage userform used to populate, edit and manipulate an excel database consisting about 160 fields (columns)... it's a big userform! One page of the userform is mainly a large multicolumn listbox which is used to display a number (up to 10) of the fields in column format. The records and fields shown can be customised by the user in accordance with his needs.

The width of each column is set dependant on the size of the data in the field. In some configurations, the total of the widths of the columns is greater than the overall width of the listbox.. but that's no great problem because the listbox simply grows a horizontal scroll bar which the user can use to see the hidden data.

I should emphasis that this listbox and the surrounding subroutines that populate and manipulate it are all working fine.

What I want to do is add a facility so that clicking the top value (a heading value) in any column causes the records to be sorted 'alphabetically' on the values in that column.

Sorting the data would be quite simple if I could reliably detect the number of the column under the cursor when tghe mouse button is clicked.

I can easily detect when the first line in the listbox is selected by using the .listindex parameter

If I use a 'Mouse_Up' or 'Mouse_Down' handler for the listbox, I can determine the column clicked by taking the 'X' parameter returned by the handler and calculating with it against the column Widths. This enables me to determine the field I want to sort by.

So far so good...

The problem arises when the horizontal scrollbar is activated and the listbox is scrolled away from the left hand edge of the box. The 'X' parameter returned by the handler is the distance in points from the left side of the box. If the listbox has been scrolled away from the left edge, the value can no longer be reliably used to calculate the column number.

If I could discover the distance, preferable in points, that the listbox has been scrolled, I could fairly easily incorporate it into the calculation and get the correct column. The problem is that I can find no way to get that value.

Any ideas???

Regards

Rob Frankham