Toggle Control & Formula

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi all,

I was wondering is it be possible to add the same value to
a range of cells on a column by using the toggle control.
What I have in column A is a range of formula, which forms
an s-curve.

What I want to be able to do is change the shape / slope
of this curve i.e. slow start, fast start, by adding or
subtracting a value, which would be operated by a toggle
control.

At the moment I have to edit each cell individually and
manually add or subtract my value to every cell which can
be a as much as fifty cells, very time consuming!

Any help very much appreciated.

Michael
 
Hi Micheal

Create a namedRange for you values in the curve. Add a
spin control to the worksheet. Create another namedRange
which is the value that you want to add / subtract from
all your curve values.

Add the following code to your spin control

Private Sub SpinButton1_SpinDown()
Dim rng As Range

For Each rng In Range("myRange")
rng.Value = rng.Value - Range("myValue")
Next
'cleanUp
Set rng = Nothing
End Sub

Private Sub SpinButton1_SpinUp()
Dim rng As Range

For Each rng In Range("myRange")
rng.Value = rng.Value + Range("myValue")
Next
'cleanUp
Set rng = Nothing
End Sub

Replace the myRange with the name of your range of curve
values, and replace myValue with the name of your value
to add/ subtract.

Richard Daniels
 
Back
Top