Hi,
I have a macro on an excel sheet that asks for confirmation before editing a cell and it works just fine. The code I use is:
Option Explicit
Dim OldValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
' COMPLICATION DATA MSG BOXES
If Not Intersect(Target, Range("C12:C23")) Is Nothing Then
Application.EnableEvents = False
Select Case MsgBox("This parameter is taken from a synthesis of the available published evidence (see the info section for details). Do you want to continue?", _
vbYesNo Or vbQuestion Or vbDefaultButton1, "Confirm change...")
Case vbYes
GoTo exit_handler
Case vbNo
Target.Value = "=Baseline_comp_PMLSE"
End Select
End If
If Not Intersect(Target, Range("E12:E23")) Is Nothing Then
Application.EnableEvents = False
Select Case MsgBox("This parameter is taken from a synthesis of the available published evidence (see the info section for details). Do you want to continue?", _
vbYesNo Or vbQuestion Or vbDefaultButton1, "Confirm change...")
Case vbYes
GoTo exit_handler
Case vbNo
Target.Value = "=comp_PMLSE*risk_ratios_comp_2_vs_PMLSE"
End Select
End If
If Not Intersect(Target, Range("G12:G23")) Is Nothing Then
Application.EnableEvents = False
Select Case MsgBox("This parameter is taken from a synthesis of the available published evidence (see the info section for details). Do you want to continue?", _
vbYesNo Or vbQuestion Or vbDefaultButton1, "Confirm change...")
Case vbYes
GoTo exit_handler
Case vbNo
Target.Value = "=comp_PMLSE*risk_ratios_comp_3_vs_PMLSE"
End Select
End If
exit_handler:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldValue = Target.Value
End Sub
End If
Now, what I'm trying to do is to write another macro that allows to reset the cells to the original values if they are modified. The new code is:
Sub cinical_data_reset()
'
' cinical_data_reset Macro
'
Answer = MsgBox("Are you sure you want to continue?", vbYesNoCancel + vbInformation, "Application Message")
If Answer = vbYes Then Else Exit Sub
'
Range("C8:C9").FormulaR1C1 = "=baseline_comp_PMLSE"
Range("E8:E9").FormulaR1C1 = "=comp_PMLSE*risk_ratios_comp_2_vs_PMLSE"
Range("G8:G9").FormulaR1C1 = "=comp_PMLSE*risk_ratios_comp_3_vs_PMLSE"
End Sub
Obviously, when I run this second macro, the msg boxes from the first macro appear (as many as the number of ranges modified...). Question is: Do any idea on how I can make the second macroto ignore the first message boxes?
Thanks a lot for your help
Bookmarks