locked
Want to lock a range of cells (multiple rows) once a button is submitted RRS feed

  • Question

  • Hi All,

    I'm working on an Excel sheet that allows shift operators to enter in their shift details, and then on submit of a custom button, it sends the data to a database for historical/reporting.

    What i would like it to do, is prevent users from editing previous submitted rows in the Excel. So once the button is pressed, the data gets submitted to a db, and the rows go to read-only.

    The next user of the sheet can only view the previous rows (see what was done in the previous shift), and they can only enter in new rows and submit.

    Any tips welcomed, thanks in advance

    Wednesday, May 2, 2018 4:52 PM

All replies

  • Hello dnaman,

    For lock a range, we need set the range locked property as true and then protect its worksheet. 

    First at all, we need set all the cells locked property in the worksheet as false, so these cells will not be locked by default. You could do this manually or a simply code.

    ActiveSheet.Cells.Locked = False

    Once you submit a custom button, you could try to unprotect the worksheet and the set locked property to the row cells you want and then project the cell.

    For instance, if you want to lock first row.

    If ActiveSheet.ProtectContents Then ActiveSheet.Unprotect
    ActiveSheet.Rows(1).Cells.Locked = True
    'below is a samply to lock multiple rows
    'ActiveSheet.Rows("2:3").Cells.Locked = True
    ActiveSheet.Protect

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 3, 2018 3:24 AM
  • Hello dnaman,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 31, 2018 7:57 AM