View original (French)

Unlock a cell in vba

Solved
Anthelm Posted messages 202 Status Member -  
Anthelm Posted messages 202 Status Member -
Bonjour,
I have a locked sheet containing a cell with a "Date" function. I want to create a macro that freezes this date by replacing it with today's date (so if I open the file the next day, the date hasn't changed)

ActiveWorkbook.Sheets("Impression").Copy
Cells("G1").Locked = False
Range("G1") = Date

The second line is causing an error. I've tried a million solutions...

The problem is that before the macro, G1 is locked, and it should be unlocked after using the macro (which first copies the document)

Thank you very much!

2 answers

  1.
    titeufdu89 Posted messages 387 Status Member 38
     
    Hello Anthelm,

    Two things
    - with the "cells" function, we code the address of the cell numerically in the form of (row address, column address) meaning that to select B1 with cells, we code cells(1,2)
    whereas if you use range, you code: range("B1")

    - The "locked" function determines whether the cell in question will be protected or not once the sheet is protected.
    To be able to modify a cell in a macro whether it is "locked = true" or "locked = false," you just need to unprotect the sheet at the beginning of the procedure and re-protect the sheet at the end of the procedure.

    Here is an example of code: 
    ActiveSheet.Unprotect Range("G1") = Date ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


    To test

    Jc
    2
    1. Anthelm Posted messages 202 Status Member 1
       
      Ah yes, I was doing it wrong!

      - The "locked" function determines whether the cell in question will be protected or not once the sheet is protected.

      Yes, thank you very much! In 3 steps, then, but it works very well :)
      0
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Good evening
    try with these two lines in your macro:
    Range("G1").Locked = False
    Range("G1").Value = Date

    although for the 1st line, it doesn’t seem necessary to unlock the cell if the sheet is not protected.
    And you won’t be able to unlock it without removing the protection
    either at the beginning of the macro:
    activesheet.unprotect
    and at the end
    activesheet.protect

    best regards
    1
    1. Anthelm Posted messages 202 Status Member 1
       
      Hi Vaucluse! I went with what Titeuf said, but thank you very much!
      0