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

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/P...cle.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

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/exce..._a_scroll_bar/

--
Regards,
John Mansfield
http://www.pdbook.com


"z.entropic" wrote:

> 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/P...cle.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

whoo-hoo! a direct hit--thanks!

z.entropic

"John Mansfield" wrote:

> 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/exce..._a_scroll_bar/
>
> --
> Regards,
> John Mansfield
> http://www.pdbook.com
>
>
> "z.entropic" wrote:
>
> > 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/P...cle.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