how to forbid saving cells on an excel calculator RRS feed

  • Question

  • Hi guys, i want to know how i can prohibit users from saving the value they entered in an excel calculator so that the next user can enter another value without having to delete the previous data.
    Friday, April 27, 2018 8:03 AM

All replies

  • Hi Abel2116

    At its simplest, all that is needed is to set the cell value where the value is entered to equal zero before the workbook is saved.

    If you have a workbook with Sheet1, cell B1 containing the formula "=2*A1", then when you enter a value into cell A1, you get the answer to 2 x the value in B1.  To clear the value from A1 before saving, enter the following code into the "This Workbook" object:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 

    Worksheets("Sheet1").Range("A1").Value = 0

    End Sub

    You simply have to substitute the range where your value is entered.

    Andy C

    Friday, April 27, 2018 8:28 PM
  • Hi Andy

    This was really helpful. Thank you Very much.

    Wednesday, May 2, 2018 11:15 AM