pac,

I thought that was taken care of by the cells.count1 line...

Anyway, use

Application.EnableEvents = False
'other code
Application.EnableEvents = True

Private Sub defaultOpt_Click()

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

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

Application.EnableEvents = False
Sheet1.defaultOpt.Value = False

Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

wrote in message
...

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

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
Isn't that what you want?


Sorry for being unclear. The problem is when I check the
check box, first defaultOpt kicks in and change the cell
back to formula. But...

The result of this change will trigger the
Worksheet_Change procedure, which switch the checkbox back
to uncheck.

The question is, how do we modify Worksheet_Change to
ignore the change made by defaultOpt procedure.

Thanks,
pac

P.S I paste the two procedures back for your convenience.

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

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



-