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

wrote:
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


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

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

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


wrote:

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

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


wrote:

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

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

Len

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


wrote:

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

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

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