I have a chart worksheet that pulls data from a data worksheet. The
number of records changes frequently so I am trying to make the chart
interactive. I have scrollbars that let me change the charted number
of records the starting and ending displayed records and the number of
sigma in the contol limits and the colum of data graphed in the middle.
I can not get the MAX value to change using the named range in the
scrollbar properties nor the VBA. The scroll bars were added with the
following code. I have named ranged for all the MAX values. I
currently delete and recreate the bars when new data is posted but that
is not what i want to have to do. i tried to access the scrollbars with
code and change the .max = but could not get the code to recognize the
control. Help me please. Thak you in advance.

Private Sub Add_Bars()
Dim OLE As OLEObject
'Adds a Scrollbar for fun
Dim mLeft, mWidth, mHeight, mtop As Double
mLeft = 100
mWidth = 526.5
mHeight = 12.75

mtop = 536.5
Set OLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Scrol lBar.1", Left:=mLeft,
Top:=mtop, Width:=mWidth, Height:=mHeight)
With OLE
..Object.Min = 2
..Object.Max = Range("N3").Value
..LinkedCell = "ZoomVal"
..Object.Orientation = 1
..Locked = True
..Shadow = True
..Object.LargeChange = 10
..Name = "ZBar"
End With

mtop = 549.75
Set OLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Scrol lBar.1", Left:=mLeft,
Top:=mtop, Width:=mWidth, Height:=mHeight)
With OLE
..Object.Min = 1
..Object.Max = Range("N4").Value
..LinkedCell = "ScrollVal"
..Object.Orientation = 1
..Locked = True
..Shadow = True
..Object.LargeChange = 10
..Name = "SBar"
End With
mtop = 563.25
Set OLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Scrol lBar.1", Left:=mLeft,
Top:=mtop, Width:=mWidth, Height:=mHeight)
With OLE
..Object.Min = 4
..Object.Max = Range("N5").Value
..LinkedCell = "OffsetVal"
..Object.Orientation = 1
..Locked = True
..Shadow = True
..Object.LargeChange = 1
..Name = "DBar"
End With
mtop = 576.5
Set OLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Scrol lBar.1", Left:=mLeft,
Top:=mtop, Width:=mWidth, Height:=mHeight)
With OLE
..Object.Min = 1
..Object.Max = Range("N6").Value
..LinkedCell = "CntrlLmtVal"
..Object.Orientation = 1
..Locked = True
..Shadow = True
..Object.LargeChange = 1
..Name = "SigmaBar"
End With

Worksheets("chart").Activate
End Sub

Private Sub Workbook_Open()
Worksheets("Chart").Activate
Worksheets("Chart").Range("N3").Value =
Application.WorksheetFunction.CountA(Worksheets("D ata").Range("A1:A65536"))
Worksheets("Chart").Range("N4").Value =
Application.WorksheetFunction.CountA(Worksheets("D ata").Range("A1:A65536"))
Worksheets("Chart").Range("N5").Value =
Application.WorksheetFunction.CountA(Worksheets("D ata").Range("A1:IV1"))
End Sub


--
CodeJunky
------------------------------------------------------------------------
CodeJunky's Profile: http://www.excelforum.com/member.php...o&userid=28896
View this thread: http://www.excelforum.com/showthread...hreadid=486462