How to allow a user to modify a cell but do not delete it?

[nangel]

I would like to allow a end user to update a excel spreadsheet by adding 1 to a cell. Please see the example below:

Example

On Monday the output was

A B C D E F
1 t1 9
2 t2 7
3
4 ttl 16
5

On Tuesday a User opens the file and adds one to "1B".

A B C D E F
1 t1 10
2 t2 7
3
4 ttl 17
5

What I would like to do verify that the user added only one to the original cell and if a mistake was made I would like for the user to be able to go back to its original number and not let the end user delete the number completely. In this case the cell should go back to (B1=9) and the user should not be able to delete it.

The code I am using to add or subtract one from the cell is:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Value = Target.Value + 1
ActiveCell.Offset(-1, 0).Select

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Value = Target.Value - 1
ActiveCell.Offset(-1, 0).Select

End Sub


Any suggestions or comments will be very appreciated.

Thank you in advance for your help!

 

[bltseattle]

Can't you just lock the cell and protect the sheet? Then the only way to change it would be through your macros and no one could delete it.

 

[nangel]

Thank you very much for your suggestion. I will not be able to use this because when the cell is protected you will not be able to click on the cell.

Any other suggestions? Thank you for your comment.

 

[1HVACEngineer]

Try this out and see if it does the job. I took the liberty of using Worksheet_BeforeDoubleClick for Adding 1 from the cell value and Worksheet_BeforeRightClick for Deleting 1 from the cell value.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Msg, Style, Title, Response
Msg = "The original value of cell " & Target.Address & _
" is " & Target.Value & ", do you want replace this " & _
" with the new value " & ActiveCell.Value + 1 & "?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "UPDATE CELL WITH NEW VALUE"
Response = MsgBox(Msg, Style, Title)
Select Case Response
Case Is = vbYes ' User chose Yes.
On Error Resume Next
Target.Value = Target.Value + 1
ActiveCell.Offset(-1, 0).Select
On Error GoTo 0
Case Else ' User chose No.
ActiveCell.Offset(-1, 0).Select
Exit Sub
End Select
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim Msg, Style, Title, Response
Msg = "The original value of cell " & Target.Address & _
" is " & Target.Value & ", do you want replace this " & _
" with the new value " & ActiveCell.Value - 1 & "?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "UPDATE CELL WITH NEW VALUE"
Response = MsgBox(Msg, Style, Title)
Select Case Response
Case Is = vbYes ' User chose Yes.
On Error Resume Next
Target.Value = Target.Value - 1
ActiveCell.Offset(-1, 0).Select
On Error GoTo 0
Case Else ' User chose No.
ActiveCell.Offset(-1, 0).Select
Exit Sub
End Select
End Sub