×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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!

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

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.

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

(OP)
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.

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources