Password protect column with unlock button

[newtoexel79]

Hello everyone,

I am trying to create a simple VBA code so that a specific column in a worksheet is unlocked when a button is presses and the password is inputted. Then I would like it to lock the column again after saving. I am really struggling to get it to work right. Basically want the column shown to be locked until the "AI Approval" button is clicked, then after they enter the password they can input information into the column below. Once they hit save it should relock the column. Thank you in advance!!

 

[DanteAmor]

Hi @newtoexel79. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Follow the instructions carefully!

1. Put the following code in your button.​

2. Change CommandButton1 to the name of your button.​

3. Change Sheet1 to the name of your sheet.​

4. Change abc for the password of your sheet.​

5. Change xyz by password to allow modifying the column.​

Private Sub CommandButton1_Click()
  Dim pwd As Variant
 
  pwd = InputBox("Please Enter password", "EDIT AI COLUMN")
 
  If StrPtr(pwd) = 0 Then
    MsgBox "Cancelled"
  ElseIf Len(pwd) = 0 Then
    MsgBox "Please Enter a valid value"
  Else
    If pwd = "xyz" Then
      With Sheets("Sheet1")
        .Unprotect "abc"
        .Range("AI:AI").Locked = False
        .Protect "abc"
      End With
      MsgBox "Now you can edit the AI column"
    End If
  End If
End Sub

6. Put the following code in Thisworkbook events.​

7. Change Sheet1 to the name of your sheet.​

8. Change abc for the password of your sheet.​

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  With Sheets("Sheet1")
    .Unprotect "abc"
    .Range("AI:AI").Locked = True
    .Protect "abc"
  End With
End Sub

Note ThisWorkbook EVENT

- Open the VB Editor (press Alt + F11).​

- Over in the Project Explorer, double click on ThisWorkbook.​

- In the white panel that then appears, paste the above code.​

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[newtoexel79]

That worked perfect!! Thank you so much!!

 

[newtoexel79]

Dante,

One more question... Is there a simple way to add in that if a value is entered into a cell in the "AI" column then it locks that entire row? I other words once the "AI" column is signed off on then that row cannot be altered?

Thank you in advance

 

[DanteAmor]

I other words once the "AI" column is signed off on then that row cannot be altered?

Your request is not very clear, I need to know:
How are your cells locked before you press the button?
What do you want to do when the button is pressed?
Do you want to lock the row after modifying a cell in the AI column?
When AI column editing is enabled, can you also modify other columns?
When you press save, what do you want to happen?

You must consider all scenarios and describe them.

I'm going to give it a try
Try the following:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  With Sheets("Sheet1")
    .Unprotect "abc"
    .Cells.Locked = True
    .Protect "abc"
  End With
End Sub

Private Sub CommandButton1_Click()
  Dim pwd As Variant
  
  pwd = InputBox("Please Enter password", "EDIT AI COLUMN")
  
  If StrPtr(pwd) = 0 Then
    MsgBox "Cancelled"
  ElseIf Len(pwd) = 0 Then
    MsgBox "Please Enter a valid value"
  Else
    If pwd = "xyz" Then
      With Sheets("Sheet1")
        .Unprotect "abc"
        .Range("AI" & .Range("AI" & Rows.Count).End(3).Row + 1, .Range("AI" & Rows.Count)).Locked = False
        .Protect "abc"
      End With
      MsgBox "Now you can edit the AI column"
    End If
  End If
End Sub

Note: you already know where to put each code.


If it is not what you need, you should create a new thread and explain in detail what you need, since it is a different topic than the one requested in the original post.

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --