View original (French)

Automatically lock cells after making an entry

Solved
mimi13580 Posted messages 72 Status Member -  
f894009 Posted messages 17417 Registration date   Status Member Last intervention   -
Hello the FORUM

I would like to know if it is possible to automatically lock cells after entering a value. If possible, display a message "e.g.: please confirm this value?".

I really want to target the cells, because the rest must remain locked and inaccessible.

Thank you very much for your help.

Attached is a model of my form: https://www.cjoint.com/?DKynfii46b8

Configuration: Windows / Mozilla 11.0

12 answers

  1.
    jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 832
     
    Hello,

    Could you explain a bit more in detail what you want to do?
    Because your file.. isn’t enough on its own...


    I really want to target the cells, because the rest must remain blocked and inaccessible.

    Already.. in Excel.. you have a "Protection" mode that allows you to choose which cells can be edited or not..

    https://support.microsoft.com/fr-fr/help/973079

    Then, via Macro.. you can intercept the modified cell(s) on a sheet and perform a specific action of your choice, such as displaying a message..

    This is handled with the method: Worksheet_Change
    Target corresponds to the modified cell... 
     Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 6 And Target.Row = 14 Then MsgBox "you modified cell F14! The new value is: " & Target.Value End If End Sub


    --
    Before asking a question, please read the site's code of conduct.
    Best regards, Jordane
    0
  2. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    If it’s for the file of your previous message (https://forums.commentcamarche.net/forum/affich-31063403-copier-effacer-et-coller-a-la-suite), it’s feasible, but you will need to protect the sheet with a password and the VBA code with the same one or a different one. Error 0 for the operators !!!!!!
    0
  3. mimi13580 Posted messages 72 Status Member
     
    Hello f894009, the forum

    No, it doesn't concern copying and pasting, erasing, for that it's the best! and thanks again....

    On the other hand, I want to set up a VBA in another file that allows me to input values into the cells, then lock them automatically after pressing Enter or moving to another cell.

    Thanks in advance
    0
    1. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 832
       
      Did you look at what I gave you?
      What did you try?
      0
    2. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
       
      Hello jordane45,

      I let you have the first pick

      mimi13580: follow jordane45
      0
  4. mimi13580 Posted messages 72 Status Member
     
    Hello jordane45

    I tried your code, but it's not what I'm looking for,

    1- I want to select a cell, for example F14
    2- fill this cell, for example 35,
    3- automatically lock this cell once I press enter or move to another cell,

    that's why I included a model.

    Thank you in advance for your help:)
    0
    1. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 832
       
      Hello,

      I tried your code, but it’s not what I’m looking for,

      No, I know that... but I’m not giving out the full code .. I point to possibilities ....
      I’m a proponent of: "I give hints ... but I don’t provide anything cooked... (or only very, very rarely...)"

      @f894009
      But if jordane45 doesn’t respond within 16 hours, then I’ll do it for you.

      If you have the time and the possibility to do it for him.. don’t hesitate.
      Originally I’m a moderator/contributor on the site codes-sources ... and our philosophy doesn’t seem to be quite the same as here... where you provide ready-made codes on request to applicants... whereas we.. (before giving them the finished work...) only help them to find it themselves...
      0
    2. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
       
      Hello,

      our philosophy seems not to be quite the same here Normally it’s the same, but from time to time I do some “ready-to-use”
      when I notice that applicants are really struggling


      Okay, I’ll do it
      bye
      0
  6. mimi13580 Posted messages 72 Status Member
     
    Hello 894009,

    Can I have your code to try???

    protect the sheet with a password and the VBA code with the same or a different one. Error 0 for the operators !!!!!!
    0
    1. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
       
      Hello,

      code not done at the moment. But if jordane45 doesn't respond by 4 PM, then I will do it for you.
      0
    2. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
       
      Hi,

      modified file, password mimi protection sheet and VBA code

      https://www.cjoint.com/?DKzqnkmk8JJ
      0
  7. mimi13580 Posted messages 72 Status Member
     
    Good evening f894009

    Thank you very much for your help, honestly nothing to say, good, quick and efficient....
    BRAVO TO YOU.

    And another 1000 thanks :)
    0
  8. mimi13580 Posted messages 72 Status Member
     
    Hello f894009, hello forum,

    I have a question about your code. On the form I did several copy-pastes in a row, so I have 3 pages. I’d like to know if I must fill in all the cells? or is there a code to provide?

    Here’s an example, thanks in advance.

    https://www.cjoint.com/?0KDqne1qnBl
    0
  9. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    Fortunately it wasn’t the same file as in a previous post!!!!!!!!!!!! 

    Private Sub Worksheet_Change(ByVal Target As Range)
     If Not Application.Intersect(Target, Range("F14:H16,F19:H19,F22:H27," & _
     "F64:H66,F69:H69,F72:H77," & _
     "F114:H116,F119:H119,F122:H127")) Is Nothing Then
     retval = MsgBox("Do you confirm this value?", vbYesNo, "DATA ENTRY VALIDATION")
     If retval = vbYes Then
     Target.Locked = True
     End If
     End If
    End Sub
    0
  10. mimi13580 Posted messages 72 Status Member
     
    Hello you and thank you for your speed and efficiency :)

    One question: is it possible to erase the content when replying to the message with "no"?
    0
  11. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello, 
    Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Application.Intersect(Target, Range("F14:H16,F19:H19,F22:H27," & _
 "F64:H66,F69:H69,F72:H77," & _
 "F114:H116,F119:H119,F122:H127")) Is Nothing Then
  retval = MsgBox("Do you confirm this value?", vbYesNo, "DATA ENTRY VALIDATION")
  If retval = vbYes Then
   Target.Locked = True
  Else
   Application.EnableEvents = False
   Target = ""
  End If
 End If
 Application.EnableEvents = True
End Sub
    0
  12. mimi13580 Posted messages 72 Status Member
     
    Wow, amazing, thank you very much

    I think I will still need your help, is it possible to talk in private?
    If not, that's okay, because I don't want to lose you in the FORUM
    -1
  13. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hi,

    go through the DMs and I’ll give you an email address
    -1