of Named Ranges, Dynamic Charts and scroll bars...

[Guest]

I can create a dynamic range chart and a scrolling chart using a scroll bar.
However, I'm stuck on the the definition of the maximum range in the scroll
bar control box, which accepts numbers only, not COUNTA...

In learing how to do those, I relied on a few great pages by our resident
MVPs:

http://www.andypope.info/charts.htm
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=518
http://www.ozgrid.com/Services/ExcelChartTools.htm
http://www.cpearson.com/excel/lookups.htm

but I was not able to blend the two tricks together..

Any tips and leads would be appreciated.

z.entropic

 

[Guest]

z.entropic,

Assuming your scrollbar is named "Scrollbar1", you might try to apply code
similar to that below:

Private Sub ScrollBar1_Change()
Dim Rng As Range
cnt = 0
For Each Rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
cnt = cnt + 1
Next Rng
With ScrollBar1
.Max = cnt
.Min = 1
End With
End Sub

An explanation can be found here:

http://www.pdbook.com/index.php/excel/automatically_updating_a_scroll_bar/

 

[Guest]

whoo-hoo! a direct hit--thanks!

z.entropic

z.entropic,

Assuming your scrollbar is named "Scrollbar1", you might try to apply code
similar to that below:

Private Sub ScrollBar1_Change()
Dim Rng As Range
cnt = 0
For Each Rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
cnt = cnt + 1
Next Rng
With ScrollBar1
.Max = cnt
.Min = 1
End With
End Sub

An explanation can be found here:

http://www.pdbook.com/index.php/excel/automatically_updating_a_scroll_bar/