G
gixasixa
Hi - Firstly sorry this is so long...Secondly thanks in advance to
anyone who can help.
I have a scroll bar on Sheet3 that controls the value in a cell F3 on
Sheet 1.
This value is then used to calculate the value of variables "invS" and
"netS".
The value of one affects invS affects the value of netS. .
ie the more money you invest the less net income you will have.
Values of these variables are then displayed in cells B11 and B12 on
Sheet 2.
Sheet 3 contains a stacked bar graphs that watch the cells B11 and B12
on sheet 2.
B11 is the top part of the graph and B12 the bottom.
As the scroll bar varies the value of "invS", "netS" SHOULD change.
BUT IT DOESNT DO SO UNTIL I TAB OUT OF THE CELL.
I need the re-calculation of "netS" and "invS"
to occur every time the scroll bar is moved and feed back to the chart
as I do it.
can anyone help?
I can email the file necessary.
The following code changes the value in the Sheet1 F3 successfully
however
THIS IS NOT THE CELL BEING MONITERED BY THE GRAPH
Therefore the change has no effect on the graph until I have tabbed out
of the cell and the other variables are calculated
POSSIBLE ANSWER: Im thinking maybe I just have to get the curser to
move to the adjacent cell then back after each
increment/decrement??????
If Im on the right track how is this done if the sheet is not the
ACTIVE worksheet? ie the curser is on Sheet 1 while I view Sheet 3
(thanks to len for the code)
Private Sub ScrollBar1_Change()
'this updates H3 with the scroll bar value
Worksheets("Info").cbxspe = "$"
Worksheets("Info").Range("F3") = ScrollBar1.Value
End Sub
Private Sub Worksheet_Activate()
'sets the scroll bar values on activation
'assumes range "AvailableIncome" is named
ScrollBar1.Min = 0 'or whatever you want it to be
ScrollBar1.Max = Worksheets("Info").Range("B3").Value
ScrollBar1.SmallChange = ScrollBar1.Max / 100
ScrollBar1.LargeChange = ScrollBar1.Max / 10
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'change the scroll bar max to the available income
'assumes range "AvailableIncome" is named
'if availableincome is a formula, remove the if...then/endif
If Target = Worksheets("Info").Range("F3").Value Then
ScrollBar1.Max = Worksheets("Info").Range("F3").Value
ScrollBar1.SmallChange = ScrollBar1.Max / 100
ScrollBar1.LargeChange = ScrollBar1.Max / 10
End If
'adjust scroll bar position to reflect
If Worksheets("Info").Range("H3").Value >= ScrollBar1.Min And _
Worksheets("Info").Range("H3").Value <= ScrollBar1.Max Then
ScrollBar1.Value = Worksheets("Info").Range("H3").Value
End If
End Sub
- Bernie
anyone who can help.
I have a scroll bar on Sheet3 that controls the value in a cell F3 on
Sheet 1.
This value is then used to calculate the value of variables "invS" and
"netS".
The value of one affects invS affects the value of netS. .
ie the more money you invest the less net income you will have.
Values of these variables are then displayed in cells B11 and B12 on
Sheet 2.
Sheet 3 contains a stacked bar graphs that watch the cells B11 and B12
on sheet 2.
B11 is the top part of the graph and B12 the bottom.
As the scroll bar varies the value of "invS", "netS" SHOULD change.
BUT IT DOESNT DO SO UNTIL I TAB OUT OF THE CELL.
I need the re-calculation of "netS" and "invS"
to occur every time the scroll bar is moved and feed back to the chart
as I do it.
can anyone help?
I can email the file necessary.
The following code changes the value in the Sheet1 F3 successfully
however
THIS IS NOT THE CELL BEING MONITERED BY THE GRAPH
Therefore the change has no effect on the graph until I have tabbed out
of the cell and the other variables are calculated
POSSIBLE ANSWER: Im thinking maybe I just have to get the curser to
move to the adjacent cell then back after each
increment/decrement??????
If Im on the right track how is this done if the sheet is not the
ACTIVE worksheet? ie the curser is on Sheet 1 while I view Sheet 3
(thanks to len for the code)
Private Sub ScrollBar1_Change()
'this updates H3 with the scroll bar value
Worksheets("Info").cbxspe = "$"
Worksheets("Info").Range("F3") = ScrollBar1.Value
End Sub
Private Sub Worksheet_Activate()
'sets the scroll bar values on activation
'assumes range "AvailableIncome" is named
ScrollBar1.Min = 0 'or whatever you want it to be
ScrollBar1.Max = Worksheets("Info").Range("B3").Value
ScrollBar1.SmallChange = ScrollBar1.Max / 100
ScrollBar1.LargeChange = ScrollBar1.Max / 10
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'change the scroll bar max to the available income
'assumes range "AvailableIncome" is named
'if availableincome is a formula, remove the if...then/endif
If Target = Worksheets("Info").Range("F3").Value Then
ScrollBar1.Max = Worksheets("Info").Range("F3").Value
ScrollBar1.SmallChange = ScrollBar1.Max / 100
ScrollBar1.LargeChange = ScrollBar1.Max / 10
End If
'adjust scroll bar position to reflect
If Worksheets("Info").Range("H3").Value >= ScrollBar1.Min And _
Worksheets("Info").Range("H3").Value <= ScrollBar1.Max Then
ScrollBar1.Value = Worksheets("Info").Range("H3").Value
End If
End Sub
- Bernie