Confirm formula change
 

Is there a way if when a user changes a cell that
contains a formula, I can pop up a msgbox to ask if they
really want to do that and give them yes/no or ok/cancel
options? Thanks.

Confirm formula change
 

The change event fires when a user edits a cell

http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

wrote in message
...
Is there a way if when a user changes a cell that
contains a formula, I can pop up a msgbox to ask if they
really want to do that and give them yes/no or ok/cancel
options? Thanks.

Confirm formula change
 

+----------------------------------------------------+
| Do you really want to that? |
+----------------------------------------------------+

That could get annoying.

As far as I know there is no way to tell what you had previously.
Besides are you trying to check if a formula is being overwritten
by a constant, or a different formula. You might want to think
of coloring your formulas, or of protecting your formulas via
sheet protection.

You could use an event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then Exit Sub
If Target.HasFormula Then MsgBox "Do you really want to do what " _
& "you just did, because it is too late, if it matters"
End Sub

Excel Developer Tip: Ensuring that Data Validation is Not Deleted
http://www.j-walk.com/ss/excel/tips/tip98.htm John Walkenbach
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

wrote in message ...
Is there a way if when a user changes a cell that
contains a formula, I can pop up a msgbox to ask if they
really want to do that and give them yes/no or ok/cancel
options? Thanks.

Confirm formula change
 

Ok, thanks. I was just wondering if there was an easy
way to keep my formulas for being accidentally
edited/overwritten by a non-savvy user. Oh well. I was
aware of sheet protection but my formulas are kinda of
scattered. I had also experimented w/ Application.Undo
and the Worksheet_Change event but didn't have any luck.
Thanks anyway.


-----Original Message-----
Is there a way if when a user changes a cell that
contains a formula, I can pop up a msgbox to ask if they
really want to do that and give them yes/no or
ok/cancel
options? Thanks.
.

Confirm formula change
 

Hi,

I was aware of sheet protection but my formulas are kinda of
scattered.
..
This should not present any problem, all of your formulas can be picked up
using the SpecialCells method.

The following routine uses sheet protection to prevent editing of your
formula cells, all other cells can be edited as usual

Sub LockFormulae()
Dim rng As Range
On Error Resume Next
Set rng = ActiveSheet.Cells.SpecialCells(xlFormulas)
On Error GoTo 0
If Not rng Is Nothing Then
ActiveSheet.Unprotect
ActiveSheet.Cells.Locked = False
rng.Locked = True
ActiveSheet.Protect
Else
'No formulas to protect!
End If

End Sub


---
Regards,
Norman



wrote in message
...
Ok, thanks. I was just wondering if there was an easy
way to keep my formulas for being accidentally
edited/overwritten by a non-savvy user. Oh well. I was
aware of sheet protection but my formulas are kinda of
scattered. I had also experimented w/ Application.Undo
and the Worksheet_Change event but didn't have any luck.
Thanks anyway.


-----Original Message-----
Is there a way if when a user changes a cell that
contains a formula, I can pop up a msgbox to ask if they
really want to do that and give them yes/no or
ok/cancel
options? Thanks.
.

Confirm formula change
 

And this could better be written as:

Sub LockFormulae()
Dim rng As Range
With ActiveSheet
On Error Resume Next
Set rng = .Cells.SpecialCells(xlFormulas)
On Error GoTo 0
If Not rng Is Nothing Then

.Unprotect
.Cells.Locked = False
rng.Locked = True
.Protect
Else
'No formulas!
End If
End With
End Sub


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi,

I was aware of sheet protection but my formulas are kinda of
scattered.
.
This should not present any problem, all of your formulas can be picked up
using the SpecialCells method.

The following routine uses sheet protection to prevent editing of your
formula cells, all other cells can be edited as usual

Sub LockFormulae()
Dim rng As Range
On Error Resume Next
Set rng = ActiveSheet.Cells.SpecialCells(xlFormulas)
On Error GoTo 0
If Not rng Is Nothing Then
ActiveSheet.Unprotect
ActiveSheet.Cells.Locked = False
rng.Locked = True
ActiveSheet.Protect
Else
'No formulas to protect!
End If

End Sub


---
Regards,
Norman



wrote in message
...
Ok, thanks. I was just wondering if there was an easy
way to keep my formulas for being accidentally
edited/overwritten by a non-savvy user. Oh well. I was
aware of sheet protection but my formulas are kinda of
scattered. I had also experimented w/ Application.Undo
and the Worksheet_Change event but didn't have any luck.
Thanks anyway.


-----Original Message-----
Is there a way if when a user changes a cell that
contains a formula, I can pop up a msgbox to ask if they
really want to do that and give them yes/no or
ok/cancel
options? Thanks.
.