dynamically set Max value on scroll bar form

Try adding this in cell D6:

=MAX(Data!C6:C2000)-COUNTA(Dashboard!$D$6:$D$15)+1

That is, D6 on the "Calculation" tab.

I need the maximum value on the forms control of the scroll bar on the dashboard tab to be dynamic.   This change does not affect the forms control property.

I added the code and created a range named "Number" with the following reference:

=OFFSET(Datal!$A$1,0,0,COUNTA(Data!$A:$A),1)

When I add a new row to the data tab the target.columns.count returns 1, therefore It exits the sub.  I am not real sure what this is counting?  How is this suppose to work?

Thanks,

I presume you are responding to me, jmkbrown ?
Read my post carefully. To clarify a little....
In Data Sheet
Select C5:Cx where x is a row number just past the last filled in cell in that column
Then Type in a name in the name box. It is the one that typically  shows the currently selected cell.
There is no formula to create... all we are doing naming the selected range of cells   This range is referenced inthe code I provided.

After things are set up, whenever you add a row to your table in the data sheet, the vba code will be triggered, the rows recounted, and the size of the scrollbar will be adjusted to accomodate the extra rows ;^)

Hope this helps.

Another thing to note here...
I assume that the way this sheet works is the user makes product-entries in the Data Table in the Data Sheet, and that one would thus be copy:inserting rows as one adds these product to the table.

If this is correct, then -->>

So for instance, say I wish to insert a row at row 19, or even copy:insert a similar product from row 8 down inbetween rows 99 and 100....
I could select the whole row (by clicking on the row number at the very left of the spreadsheet), grab the row by an edge with the mouse while holding the control and shift keys, and then dragging it down to the next row edge, or any other row location in the table for that matter, and release...

This will copy:insert a row where I perform this action.
HOWEVER, a problem exists that the numbering in Column C will eventually break, meaning it will lose its sequential number tracking, when using the forumla that is currently in there... which is =Sum(this cell's relative reference plus the value in the one above it).

That formula will not serve this purpose because using relative referencing to itself or an adjacent cell cannot maintain proper integrity during said copy actions.

Therefore, what is needed (commonly used) in this type of situation is a formula that is more along the lines of
=Sum(this cell value plus the one above it)
  This may seem the same, but it is not, as there is no relative reference being made here... but a direct self-reference 'only' ...for setting up the relative relationships...

The correct formula uses the Offset function, like this...
=SUM(OFFSET(C7, -1, 0), 1)
...where in this example the forumla resides in cell C7.
You can copy that anywhere and it will always do the same thing!  It will always look in the cell above itself, add 1 to the value there, and display that result.

==================
So, to change this in the sheet you posted, I would recommend placing that forumla in there (at C7 in the Data Sheet), and then copy it down to the last populated Cell in the Table (using the fill-down command).

From then on, anytime you copy:insert a row [to anywhere in the table], the proper reference will always be applied, and the row numbering will always be applied correctly and remain consistent  ;^)

===
A bit long, but I wanted to be sure to include all the gory details for proper reference (for anyone).

Cheers, HTH

twohawks, this works great!!   The row numbering formula is awesome!

My actual dashboard tab will contain multiple scroll bars based upon multiple data tabs  (Data1, Data2....)

How do I identify which scroll bar to update?

Glad you find that helpful, jmk.

I found getting the ident on that scrollbar challenging, as those scrollbars are not vba controls, but drawing objects.
The way I did it was to record a macro that did something with that object, and then observe the ident in the macro ;^)  I suppose you could write a macro to iterate the Shapes collection and spit out their idents if you had lots of them.

It would be nice to know if there is a "proper" way to ident these... I didn't spend time hunting down the answer elsewhere, maybe someone will chime in.

Thank you very much!  This was very helpful.  

If I find a good way to ident the shapes I follow up.

yw, jmb.

btw,
The answer to the dangler is... the identifier for the selected shape object should appear in the Namebox (to the left of the function bar).  Why I didn't see it before...?
Too simple, eh?
Well actually a lot of people have struggled with this issue of shape properties.
So I for posterity I would likw to add the following links to this answer thread:
http://www.ozgrid.com/VBA/shapes.htm
http://www.mvps.org/dmcritchie/excel/shapes.htm

Lots of shape mani[pulation and info gathering techniques ;^)
Cheers.

You are right, that is way to simple!  Thank you very much for the update!

Now I have tried to add the above code to a macro I created for my actual document, but it is not working.  When I run the code I get the error:

Unable to set the Max property of the ScrollBar Class.

I tried the exact code from up above, but it did not work either.  

Should I start a new question?

Thanks in advance for any help.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/21/2010 by Joan Brown
'
'
    Dim shp As Shape
    
    Sheets("By Reading Dr").Select
    Application.Goto Reference:="R5C2"
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Sheets("Dashboard").Select
    Range("A1").Select
    
    Set shp = ActiveSheet.Shapes("Scroll Bar 1")
    Sheets("Calculation").Select
    If Range("D1") > 9 Then
        xx = Range("D1").Count - 11
        With shp
            .ControlFormat.Max = xx
        End With
    End If
End Sub

Select allOpen in new window

Well, I don't know what a moderator will tell us, but I am willing to look at this (don't see any reason to lay down a bunch of points on it if we are simply wrapping an answer up)... only thing is there are a number of things I would have to assume not having the referenced sheet, pivot table, etc...

First, Do you know how to setup watches and step thru your code in the vb editor?
If not, please do a little bit of internet searching on
excel how to debug vba

...and learn some basics for stepping thru your code and debugging.  You will be glad you did.
I would recommend opening (minimally) the following views:
"Properties"  (I place mine inthe lower left)
"Locals"   (I place this in the right edge upper)
"Watches   (I place this in the right edge lower)

If you already know a little about these things, let me know what you have done, and if the error is occuring at the initiation of the code, or at what line it is happening.

I didn't think about doing that.  watching the code worked.  It was a problem with the code.  I changed line 18 to:

xx = Range("D1") - 11

Thanks for all of your help  it is greatly appreciated!

You are welcome.... gladd you worked it out!
Thanks for the points ;^)