Adding a scroll bar to a dynamic chart

[hallingh]

Hi all.

I have a worksheet that uses a horizontal bar graph to display data that is contained in a named range. Currently, there are only 12 values to display in the range which can fit nicely into the chart. However, as we add more and more data, the number of values will increase significantly. I'm using a drop down to filter by time period (e.g. values entered within the last 3 months, 6 months, 9 months, etc.) but one of the choices will also be all data. I want to add a scroll bar so the user can scroll up and down the graph to see all hte values, but don't really know where to start.

Should I use a form control scroll bar to do this? If so, how to I make a form control scroll bar functional so it will allow the user to scroll through the different values? I know I haven't given you much to work with, so simple broad instructions and general background info on adding this sort of a scroll bar would be GREATLY appreciated. Thanks a ton for the looks.

Hank

 

[jeffreybrown]

Hi Hank,

Maybe this is a start...

http://www.andypope.info/charts/Scrolling.htm

 

[hallingh]

Thanks for the link. I'll look it over now and see what I can do.

Hank

 

[jeffreybrown]

Here's another great resource.

http://www.datapigtechnologies.com/Webinars/10MustHaveVBASkills.htm

 

[hallingh]

I can't run the second resource; I'm at work and can't run the add-on that is required to view the video. I've looked over teh first file you showed me, and I have a couple questions on it.

First, How does the value in E2 change? It says the scroll bar is linked to E2, but how is it linked? I guess what I'm trying to figure out is how all of this is connected to show how this sheet is built. It is pretty much exactly what I want, but it doesn't provide all that much detail on how it is actually built.

Thanks again for the link.

Hank

 

[jeffreybrown]

Hi Hank,

The scroll is linked to E2 in the properties.

On the Developer tab >> Design Mode

Right click the scroll bar >> Properties

Categorized >> Misc >> LinkedCell >> E2

 

[hallingh]

Perfect. Thanks a lot for the help; that link was great. The scroll bar on my sheet is good to go.

Thanks!

Hank

 

[jeffreybrown]

Great Hank...glad you got what you needed...thanks for the follow-up