Objects, Cell Referencing and Sorting

[Dreiding]

This is an extension of another question I posted on Scrollbars. I am using
Excel 2003. Here's the situation I need help on.
I have table with a scrollbar in each cell of a column. Each scrollbar is
linked to the cell under the object. My problem when I sort my table, the
scrollbar cell reference is updated to sorted location of the cell. I need it
to remain fixed.

How do I fix the scrollbar cell reference to be the cell beneath it with no
impact due to sorting?

Thanks,
- Pat

 

[JLGWhiz]

It might be easier to copy your data to another range to do the sorting and
then paste it back into the table. You would have to work with values only.
That would leave the scroll bars intact. This assumes that there are no
formulas in the applicable range.

 

[Dreiding]

Yes, I see how this could work. Unfortunately I have equations in the table.
Also the user will need the capability to sort on different fields.
Can a formula be entered in the Linked Cell field of the Scrollbar property?

Thanks,
- Pat

 

[Dreiding]

I figured a method to to do this as long as I force the users to do the
sorting through my macro's and not directly through the excel sort
capabilities. It works, but the user still can screw themselves if they use
other sort methods

Do anyone know how to trap the excel sort command? If this can be done, I
can insert the sorting and add the fix to the object linked cells
automatically and total avoid the user getting themselves in trouble.

Thanks,
- Pat