MsgBox to Confirm Change

[matrix1405]

I have formulas in my speadsheet that I cannot protect due to the fact that the user will need to change the cell from time to time. I would like a msgbox to appear everytime they try to change the value of a cell, stating "Are you sure you want to change the value of this cell?", if user answers yes the value is changed, if no the value stays the same. I need this to apply for the range of cells $D$2:$AH$500. Please Help!

 

[njimack]

Welcome to the board. Give this a go - paste it into the module of the sheet in question...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim confirm As Variant

Application.EnableEvents = False

If Intersect(Target, Range("D2:AH500")) Is Nothing Then Exit Sub

confirm = MsgBox("Are you sure you want to change the value of the cell?", vbYesNo, "Formula overwritten!")

If confirm = vbNo Then Application.Undo

Application.EnableEvents = True

End Sub

 

[mikerickson]

Try putting this in the sheet's code module.

Dim LastValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Cells.Count = 1 And (Not (Application.Intersect(Target, Range("d2:ah500")) Is Nothing)) Then
            If LastValue <> .Value Then
                If MsgBox("Confirm Change", vbYesNo) = vbNo Then
                    On Error GoTo ErrorHalt
                    Application.EnableEvents = False
                    .Value = LastValue
                End If
            End If
        End If
    End With
ErrorHalt:
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    LastValue = Target.Value
End Sub

 

[matrix1405]

Thanks! Works perfect