lock row and ask password to unlock all the sheet

  • Hi,

    I'm far from being the best in VBA code and I'm trying to create a code that I know is not that hard to do but I'm stuck everywhere. Let me explain

    I have 27 column (A : AA)

    I have X numbers of row (not important)

    What I want :

    STEP 1 : If I enter "test" or if the cell is red in "AA" it lock all the row

    STEP 2 : if I click anywhere in this row it ask for a password and if the password is goos it ulock the row rand remove the red

    *note* even if for exemple the row 12 is locked I still can edit anything in other row

    thanks in advance if anyone can help me

    I tried a lot of code and the nearest one I found and tried are these ones :

    Sub password()
    Dim pass As String
    pass = InputBox("Enter the password to unlock the sheet")
    If pass = "test1" Then
    activesheet.protect = False Else
    Exit Sub
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim N As Long
    N = Target.Row
    If Intersect(Target, Range("AA:AA")) Is Nothing Then Exit Sub
    If Target.Text <> "REPORTÉE" Then Exit Sub
    Range("A" & N & ":Z" & N).Locked = True
  • Try this

    I don't think changing the cell colour will fire the change event though.

  • Thanks for the informations royUK.

    I tried the code, it locked as supposed but there was 2 problems. First the "X" message always appear non stop and it lock all the document everytime not just the Row. I tried this

    This way If I enter the TEST or if it's red it block and prompt a message asking for the password but I have the same problem I has with every code, everytime and the problem is if I lock a Row, shince the sheet is protected it blocked every row in the document and not just the specific row. Do you know a workaround ? thnkas in advance

  • Using VBA without knowing ho Excel works is a little pointless.

    1. All cells in an Excel sheet are by default locked Before protecting a sheet you need to select all cells in that sheet by clicking the small box at the junction of the Headings and Row Numbers, to the left of A.
    2. Right click any cell, choose Format Cells -> Protection. Then uncheck the Locked cells.
    3. Click OK
    4. If you have any cells that need to be Locked before running the code then select the cells and format the cells to be Locked using the previous two steps.
    5. Protect the sheet using a password if required.
  • I know that and I know hot to protect or unprotect a sheet in excel. The thing is it didn't lock a row it lock everything the exact opposite of what I was asking for help. Just for exemple I want my code to the THE SAME THING as this one Lock row after pressing save but not when I press save but when I enter a specific "string" un AA column.

  • The X message just needed to be removed.

    The code locks the row that contains test when entered, all other unlocked cells remain unlocked.. As I said, I don't think changing the colour of the cell can trigger the code

    When trying yo change the locked cells Excel generates a message.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!