-----Original Message-----
pac,

In the worksheet's codemodule, use this:

Private Sub defaultOpt_Click()
ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize
(14, 1).Formula = _
"=Actual FORMULA HERE, written for cell D15"
End Sub

Thanks for the response. It works to some extent. Still
some problem (design problem really). When the check box
is clicked, the formula is reinstated, and as a result,
some of the values oif the cells in the range was update.
This causes a collision between the two Sub's.

I need to add another if condition in the second Sub to
avoid changes made by the formula itself. Any diea?

Thanks much,
pac





Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is
Nothing Then
Sheet1.defaultOpt.Value = False
End If
End Sub

HTH,
Bernie
MS Excel MVP

"packat" wrote in
message
...

I have an array of formulas.
I wanted to enable manual user input on these array but
still preserve the formula. So I added a checkbox.
(DefaultOpt).

If the box is checked the formula is restored into the
cells in the array. The following code seems to work.
(But
suggestion for any improvement is welcome. I am sort of
green at Excel/VBA)

----
Private Sub defaultOpt_Click()
For i = 15 To 28
ActiveWorkbook.Worksheets("Model 2").Cells(i,
4).Formula = "=FORMULA HERE"
end sub
----

However, I also want to make sure that, if the value of
a
cell in the array is changed, the box should become
unchecked.

Any suggestion how I proceed to do this?
Thanks in advance,
pac









.