VBA access to Active X Scroll Bar

[CodeJunky]

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.ScrollBar.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.ScrollBar.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.ScrollBar.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.ScrollBar.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("Data").Range("A1:A65536"))
Worksheets("Chart").Range("N4").Value =
Application.WorksheetFunction.CountA(Worksheets("Data").Range("A1:A65536"))
Worksheets("Chart").Range("N5").Value =
Application.WorksheetFunction.CountA(Worksheets("Data").Range("A1:IV1"))
End Sub

 

[Dave Peterson]

I'm not sure if I understand, but...

You have a Data worksheet that allows users to enter more and more stuff. You
want the Max for some scrollbars on sheet Chart to change when the number of
entries changes (increases or decreases)??

If that's close, you could use a worksheet event that adjusts the maximum value
for the scroll bar.

I'm sure I didn't use the correct ranges for each max, but it'll give you an
idea.

Rightclick on the Data Worksheet tab. Select view code and paste this into the
code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Union(Me.Range("a:a"), Me.Range("1:1"))) Is Nothing
Then
Exit Sub
End If

With Worksheets("Chart")
.ZBar.Max = Application.CountA(Me.Range("a:a"))
.SBar.Max = Application.CountA(Me.Range("1:1"))
.DBar.Max = Application.CountA(Me.Range("a:a"))
End With
End Sub

It looks for changes in column A or row 1. (I didn't guess at SigmaBar.)

 

[CodeJunky]

I was trying to figure out the Active X syntax for accessing the
controls values in VBA. I figured it out by Googling a similar
problem. Some of the Object attributes are in themselves object
attributes. Just means that you have to use object twice in a row to
drill into those values. Like object.object.max = number


This is what I ended up doing incase someone else runs into a similar
problem and needs the answer...


Code:
--------------------
Private Sub Workbook_Open()

Application.ScreenUpdating = False
Dim maxrow As Long
maxrow = Application.WorksheetFunction.CountA(Worksheets("Data").Range("d:d"))
'msgbox maxrow
Dim ws As Worksheet
Dim obj As OLEObject
' Save a reference to Sheet 1.
Set ws = Sheets("Chart")
ws.Activate
ws.Range("N3").Value = maxrow
ws.Range("N4").Value = maxrow
ws.Range("N5").Value = Application.WorksheetFunction.CountA(Worksheets("Data").Range("A1:IV1")) - 4 ' delete the header space

' List information about OLE objects.
' MsgBox "Number of OLEObjects = " & ws.OLEObjects.Count
' Loop through the OLE objects.
On Error Resume Next
next_row = 2
For Each obj In ws.OLEObjects
With obj
If .Name = "ZBar" Then
.Object.Max = ws.Range("N3").Value
End If
If .Name = "SBar" Then
.Object.Max = ws.Range("N4").Value
End If
If .Name = "DBar" Then
.Object.Max = ws.Range("N5").Value
End If
If .Name = "SigmaBar" Then
End If
' MsgBox " NAME: " & .Name & vbCr & " MIN: " & .Object.Min & vbCr & " MAX: " & .Object.Max & vbCr & " Linked Cell: " & .LinkedCell
End With
' Move to the next row.
Next obj

Set ws = Sheets("Data") ' add header info for chart constants
ws.Range("A2:A" & maxrow).Formula = "=B2+Chart!$B$6*STDEV(OFFSET($D$2:$D$65536,,OffsetVal,,))"
ws.Range("b2:b" & maxrow).Formula = "=AVERAGE(OFFSET($D$2:$D$65536,,OffsetVal,,))"
ws.Range("c2:c" & maxrow).Formula = "=B2-Chart!$B$6*STDEV(OFFSET($D$2:$D$65536,,OffsetVal,,))"
Sheets("Data").Select
Set ws = Sheets("Chart")
ws.Activate
Application.ScreenUpdating = True
End Sub