Need help with a macro

[Guest]

I have a worksheet that is used for data entry, then its puts that in a other
report spread sheets well, a few of the cells will rarely be changed. I
would like a message box to pop up and Ask if the user is sure they want to
change the information in the cell. Than have the option to say yes or no.
But I have no clue how to do that. Thank you for the help.

 

[Guest]

Try putting this is in the code for the sheet:
-------------------------
Private SaveValue

Private Sub Worksheet_Activate()
SaveValue = ActiveCell.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Static Mech As Boolean
If SaveValue = "" Then Exit Sub
If Mech = True Then
Mech = False
Exit Sub
End If
If MsgBox("Are you sure you want to change that value?", vbYesNo) = vbNo
Then
Mech = True
Target.Value = SaveValue
End If
End Sub

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

What it does is to save the value of every cell when the user moves there.
This is what it will recall if the user does not want to change the value.
The variable "Mech" is there just so that you don't wind up with changing it
back creating another change event and looping.

There are a couple of problems with this.

First, if you go into the macro editor, reset the program and then go back
to your sheet, SaveValue will not be initialized. Second, whenever the value
is reset (by the user clicking no), the undo stack will be emptied.

Good luck

 

[Guest]

Thanks that worked perfect.

Try putting this is in the code for the sheet:
-------------------------
Private SaveValue

Private Sub Worksheet_Activate()
SaveValue = ActiveCell.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Static Mech As Boolean
If SaveValue = "" Then Exit Sub
If Mech = True Then
Mech = False
Exit Sub
End If
If MsgBox("Are you sure you want to change that value?", vbYesNo) = vbNo
Then
Mech = True
Target.Value = SaveValue
End If
End Sub

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

What it does is to save the value of every cell when the user moves there.
This is what it will recall if the user does not want to change the value.
The variable "Mech" is there just so that you don't wind up with changing it
back creating another change event and looping.

There are a couple of problems with this.

First, if you go into the macro editor, reset the program and then go back
to your sheet, SaveValue will not be initialized. Second, whenever the value
is reset (by the user clicking no), the undo stack will be emptied.

Good luck

 

[Guest]

If I delete multiple cells it gives an error message. Is there a way to fix
it?