Increase/Decrease Value of variable/cell using scroll bar?

[gixasixa]

Hi,

is there a way to control the value of a variable or a cell value by
using a scroll bar?
I have 3 stacked bar graphs that compare the value "Amount Invested"
with "Net Income"
of 3 different strategies.

From the worksheet holding the graph I would like to be able to

increase/decrease the value of "Amount Invested", this value will
affect the "Net Income", and view the results.

ie I have a cell say B2, this is the data series for the top part of
one of the bar graphs "Amount Invested".
I would like to be able to increase/decrease this value by 1% for each
1% move in either direction and be able to watch the graph change as I
do this.
The upper limit for the value would be detirmined by the available
income.
When the value in B2 is calculated I would like the sheet to
automatically calculate the max amount that can be contributed and work
out what percentage B2 is of that notional maximum and put the scroll
bar in the relevant position to represent the current value.

Is this possible?
thanks in advance!
Bernie

 

[LenB]

Hi,

is there a way to control the value of a variable or a cell value by
using a scroll bar?
I have 3 stacked bar graphs that compare the value "Amount Invested"
with "Net Income"
of 3 different strategies.
increase/decrease the value of "Amount Invested", this value will
affect the "Net Income", and view the results.

ie I have a cell say B2, this is the data series for the top part of
one of the bar graphs "Amount Invested".
I would like to be able to increase/decrease this value by 1% for each
1% move in either direction and be able to watch the graph change as I
do this.
The upper limit for the value would be detirmined by the available
income.
When the value in B2 is calculated I would like the sheet to
automatically calculate the max amount that can be contributed and work
out what percentage B2 is of that notional maximum and put the scroll
bar in the relevant position to represent the current value.

Is this possible?
thanks in advance!
Bernie

Sure, anything is possible!
Put a scrollbar1 on the sheet containing your data, and paste this into
the worksheet code of that sheet. I've assumed a named range for
"AvailableIncome". If not, just change the three places in the code to
your range. This should get you started anyway. I didn't try it with a
chart, but it does change the cells value so it should do what you want.

Len


Private Sub ScrollBar1_Change()
'this updates B2 with the scroll bar value
Range("B2").Value = 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 = Range("AvailableIncome").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 = Range("AvailableIncome") Then
ScrollBar1.Max = Range("AvailableIncome").Value
ScrollBar1.SmallChange = ScrollBar1.Max / 100
ScrollBar1.LargeChange = ScrollBar1.Max / 10
End If

'adjust scroll bar position to reflect B2
If Range("B2").Value >= ScrollBar1.Min And _
Range("B2").Value <= ScrollBar1.Max Then
ScrollBar1.Value = Range("B2").Value
End If

End Sub

 

[gixasixa]

Thanks Len! - I will give it a go and let you know how I went!

 

[gixasixa]

Awesome Len - it worked - I have to make some more tweaks but otherwise
you were spot on. You a legend! thanks

 

[gixasixa]

Awesome Len - it worked - I have to make some more tweaks but otherwise
you were spot on. You a legend! thanks

 

[LenB]

You are welcome! Glad I could help. You had a neat idea with the
scrollbar that I may use in the future too.

Len

 

[gixasixa]

dont know why my post appears more than once.....
anyway thanks

 

[gixasixa]

dont know why my post appears more than once.....
anyway thanks