Lock and protect selected excel rows with VBA code

Hi Gurus,

I've an excel worksheet with sama data to be protected and some data to be updated by our users on a regular basis.

My query is, will it be possible to use VBA code to password protect any selected row after updating? Meaning, once the user has finished updating that particular row, maybe with a click of a button, that row will be procteted and no additional change allowed. Only admin who has the password will be able to make any additional changes.

However, the rest of the row will still be accessible and updated by user till it's protected.

Appreciate for your help.

Thanks.


- Jack

Yes!

That can be done.. Can you attach a sample workbook?

Hi Gurus,

I've attached a sample data in this post.

Users need to update rows in B2 to D11, yellow highlighted.

Once update is done, the row will be protected as explained previously.

Thanks for your help.


- Jack

So will the user fill in 1 row at a time and then that particular row is to be locked, or will the user fill in all the rows from 2 to 11, and then all the rows should be locked at once?

Hi Gurus,

User will need to update 1 row at a time, once done, that particular row will be locked.

Thank you.


- Jack

Hi,

Please see the file attached.. The password to unlock the sheet is asd

Let me know if it is as required.

Do not forget to enable Macros..

Hi, SmallJack,

Right-click on the worksheet-Tab, choose View Code and enter the following code into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column >= 5 And WorksheetFunction.CountBlank(Range("B" & Target.Row & ":E" & Target.Row)) = 0 Then
  ActiveSheet.Unprotect
  ActiveSheet.UsedRange.Locked = True
  ActiveSheet.Protect "password"
End If
End Sub

Code will be triggered f any column form E on is entered or changed and will lock Columns B to E.

Ciao,
Holger

Hi Holger,

Thanks for your help.

I've tried, but it locked all the rows, including those raws which have not been updated.

I've attached my test data, I'm unable to update row 7 onwards.

Please advice. Thanks.


- Jack

Originally Posted by SmallJack

Hi Holger,

Thanks for your help.

I've tried, but it locked all the rows, including those raws which have not been updated.

I've attached my test data, I'm unable to update row 7 onwards.

Please advice. Thanks.


- Jack

Hi,

Did you happen to check out my solution?

Hi, Jack,

jusrt unprotect the sheet again, run test and then use the altered Code behind the Sheet:

Sub test()
Range("A1:E" & Rows.Count).Locked = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column >= 5 And WorksheetFunction.CountBlank(Range("B" & Target.Row & ":E" & Target.Row)) = 0 Then
  ActiveSheet.Unprotect "password"
  ActiveSheet.Range("B1:E" & Target.Row).Locked = True
  ActiveSheet.Protect "password"
End If
End Sub

Besides: you should be more precise on what you really want. Iīm out of this thread.

Ciao,
Holger

Hi Gurus,

Thanks for the reply.

I've tried your solution, but there are some issues.

Users are only allowed to update the yellow highlighted area only.

I've tried to lock outside yellow area with protection, but when I click on the button, it removes them.

Also, I hit with an error when trying to update raw 7, see attached (SampleData-Test2.xls).

The code should be able to cater the ongoing update within the yellow area.

Appreciate for your help.

Thank you.


- Jack

Check it out now

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngCtr As Long

Const cstrPW As String = "password"

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B2:D11")) Is Nothing Then

  For lngCtr = 2 To 11
    If WorksheetFunction.CountBlank(Range("B" & lngCtr & ":D" & lngCtr)) = 0 Then
      ActiveSheet.Unprotect cstrPW
      Range("B" & lngCtr & ":D" & lngCtr).Locked = True
    Else
      Range("B" & lngCtr & ":D" & lngCtr).Locked = False
    End If
  Next lngCtr
  ActiveSheet.Protect cstrPW
End If

End Sub

@NeedForExcel:
Please enter Test into Cell B11 alone and run your code in your attachment. I doubt that all empty rows between 3 and 10 should be locked as well.

Ciao,
Holger

Originally Posted by HaHoBe

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngCtr As Long

Const cstrPW As String = "password"

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B2:D11")) Is Nothing Then

  For lngCtr = 2 To 11
    If WorksheetFunction.CountBlank(Range("B" & lngCtr & ":D" & lngCtr)) = 0 Then
      ActiveSheet.Unprotect cstrPW
      Range("B" & lngCtr & ":D" & lngCtr).Locked = True
    Else
      Range("B" & lngCtr & ":D" & lngCtr).Locked = False
    End If
  Next lngCtr
  ActiveSheet.Protect cstrPW
End If

End Sub

@NeedForExcel:
Please enter Test into Cell B11 alone and run your code in your attachment. I doubt that all empty rows between 3 and 10 should be locked as well.

Ciao,
Holger

Hi Holger,

I have kept that in mind, however keeping in mind, normally data entry will happen one row after another, that's why like that..

Hi, NeedForExcel,

no need for a full quote here - maybe have a look into the Forum Rules (#12).

Any entry into Column B would have to leave Column C and D open for more entries in that row or how will you fill the entries in these columns when the row is locked?

Ciao,
Holger

Originally Posted by HaHoBe

Any entry into Column B would have to leave Column C and D open for more entries in that row or how will you fill the entries in these columns when the row is locked?

Agreed! But the user will lock the Row only when he finishes filling all the 4 Columns on that particular row wont he? Atlease that's what I think.

Best if the OP states if he requires something otherwise.

Hi All,

Thank you for your help and replies.

Greatly appreciate it.



- Jack

Hi Gurus,

How to incorporate solution given by Holger (post #13) into using a button to trigger the password lock?

Thought it'll be more user friendly for users to manually lock the entry only when they've really finished entering the data.

Thank you.



- Jack

Hi, Jack,

Thought it'll be more user friendly for users to manually lock the entry only when they've really finished entering the data.

Sorry but to rely on extra action isnīt what I would call user-friendly.

Sub LockSheet()

Dim lngCtr As Long

Const cstrPW As String = "password"

For lngCtr = 2 To 11
  If WorksheetFunction.CountBlank(Range("B" & lngCtr & ":D" & lngCtr)) = 0 Then
    ActiveSheet.Unprotect cstrPW
    Range("B" & lngCtr & ":D" & lngCtr).Locked = True
  Else
    Range("B" & lngCtr & ":D" & lngCtr).Locked = False
  End If
Next lngCtr
ActiveSheet.Protect cstrPW

End Sub

Ciao,
Holger

Hi Holger,

Great, works perfectly.

Thanks for your help and replies.

Have a nice day.

Hi, Jack,

maybe move the command to unprotect the sheet before the loop instead of using it inside there (itīs needed just once).

Thanks for the fedback and the rep.

Ciao,
Holger

Hi Holger,

Thanks for the update.

With the given code, but not using a button trigger method, can I check if it’s possible to protect multiple worksheets on opening or closing the excel file? For example of multiple worksheets: Sheet1, Sheet2, Sheet3 and soon.

Appreciate for your help.

Thank you.

Hi Gurus,

Anybody else can help out? Thanks.

Hi All,

Never mind, closing the thread now.

Thanks everyone