View original (French)

Read-only and inability to access a cell

Solved
Sara71170 Posted messages 5 Status Member -  
Sara71170 Posted messages 5 Status Member -
Hello,

I would like, when my file is read-only, for no one to be able to modify anything.
Sometimes, we forget that we are in read-only mode and think we are making changes.
At the moment of saving, horror, we have to start all over again and make sure we haven't missed anything...

Is it possible to lock the cells in read-only mode, for example with an error message?

Knowing that we have Excel 2013

Thank you very much for your help

--
Sara

2 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    during sheet protection, you have the option in the displayed list to restrict access to cells and thus prevent modifications. Except, of course, in unlocked cells.
    best regards

    --
    To err is human, to persist is diabolical.
    0
    1. Sara71170 Posted messages 5 Status Member
       
      Thank you for your response, but actually, I don't want a password.
      Let me explain quickly.
      We have a shared schedule where everyone can add or modify orders.
      However, it happens (far too often) that people who are not proficient in Excel don't realize (or forget) that they are in read-only mode.
      As a result, they make modifications and have to start over (with the risk of errors).
      That's why I would like, in read-only mode, for us to be unable to enter any cell. Basically, it should be like a photo.

      I don't know if I was clear... and if it's possible.

      Thank you anyway for your response, and if you have the solution, that would be WOW.
      0
  2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    An alert message that appears with each modification.
    Macro to be placed in ThisWorkbook. 
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If ThisWorkbook.ReadOnly Then MsgBox "Workbook is read-only!" & vbLf & "Your changes will not be saved." End If End Sub

    eric

    By trying continuously, you ultimately succeed.
    So the more it fails, the more chances you have that it will work. (the Shadoks)
    In addition to the thank you (yes, it is done!!!), remember to mark it as resolved. Thanks
    0
    1. Sara71170 Posted messages 5 Status Member
       
      Thank you Eriic, but unfortunately this code does not meet my needs. Indeed, it simply displays that the file is already open but does not lock the cells, so I can still write in them.
      The goal is really to not be able to write in them, just to see.

      Thank you anyway. That's very kind.

      Sara
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      If the file is read-only, they are notified with each entry that they will lose their changes.
      Are they really that thick not to understand and continue?
      What would it add to prevent input other than adding more lines of code?
      eric
      0
    3. CarineVL Posted messages 342 Status Member 9 > Sara71170 Posted messages 5 Status Member
       
      Good evening,
      If it needs to be locked like a photo, it means locking the entire sheet...
      Click on the top left corner,
      “Cell format”
      “Protection”
      check “Locked”
      Go to “Review”
      “Protect the sheet”
      Set a password or not
      OK
      No matter what we do in this mode, we will only be able to view the sheet without modifying any of the cells.
      Sincerely
      CVL
      0
    4. Sara71170 Posted messages 5 Status Member
       
      Hello,

      Erric!!! lol!!! Yes, they are totally rough, we work in construction!!! I thought I was going to die laughing reading this comment because it's so true!!!
      But I had made a mistake when typing your program and indeed it works very well, it writes to me perfectly "your changes will not be saved"
      So, that's exactly what I wanted!
      But there's a big but, I tested it twice and it crashes my computer every time. I have to force Excel to shut down....

      We're almost there....

      Eric, you're one step away from being my savior!!!

      Sara
      0
    5. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hello,

      I don't think it's this simple line of code that's causing Excel to crash; there must be a problem with your file.
      Try to rebuild it in a new file by copying the cells (not by copying the sheets!) and let me know if it crashes again.
      Otherwise, try in a new empty workbook and let me know.
      Eric
      0