Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to confirm entry in Col "C" is valid for data in Col "A" | Excel Discussion (Misc queries) | |||
Confirm a choice before Macro | Excel Discussion (Misc queries) | |||
Confirm before deleting a worksheet? | Excel Discussion (Misc queries) | |||
Confirm deletion | Excel Programming | |||
Please confirm the following bug in Excel | Excel Programming |