activex scroll bar

[charly1]

Hi all

I have an ActiveX scrollbar control on my worksheet. It has been used to create a "scrollable table". (Using the offset function, which returns the data from a range of cells that contain a data spill).
I would like the max value for the scrollbar to be a dynamic number, always referring to the currently last cell of the spilled data. Meaning that I would never be able to actually scroll down into empty cells.

Any help would be greatly appreciated!

Thanks in advance!

 

[RoryA]

You'd need code to do that. An alternative would be to alter your OFFSET formula to limit the rows returned based to the lesser of the scrollbar value and the actual data count.

 

[charly1]

Thanks for your quick reply.

I understood that I would need code, my problem was how exactly to write that code?

 

[RoryA]

Probably something like a Worksheet_calculate event - for example:

private sub worksheet_calculate()
me.scrollbar1.max = range("A1").spillingtorange.rows.count
end sub

 

[charly1]

Probably something like a Worksheet_calculate event - for example:

private sub worksheet_calculate()
me.scrollbar1.max = range("A1").spillingtorange.rows.count
end sub

Thanks So Much for your time. It worked perfectly.

Quick q, was there a specific reason why you wanted to place the code in a worksheet calculate event and not in a scrollbar change event?

 

[RoryA]

If it's in the Change event, it won't actually update until after you scroll, which could lead to some odd effects. In the calculate event, it will be updated as soon as the source data changes.