• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Row to lock based on column value

gguglani

New Member
Facts:

We have a protected worksheet wherein all rows represent data pertaining to one client invoice

e.g. Row 3 have data pertaining to Invoice No. 1 from column A to X

wherein data can only be selected from drop down lists in column A to J (unprotected cells)

Column K to X are protected cells which calculate or pick the data from other sheets based on drop down selected in column A to J

(similarly row 4,5,6,.... have data pertaining to invoice no. 2,3,4,......)


In column Y we have header as "Printed" and data can be selected from drop down as either "Yes" or "No" or can be blank ""


Query: How to lock the complete row (only 1 selected row) when the value column is selected as "Yes"

(For any error caused by user, only admin has the sheet password to unlock and rectify the error).


PS: I have tried taking the cue from earlier post but couldn't find the solution. Apologies in case solution is already given by learned members in the forum.
 
Would you like pasting a sample workbook for better and quick solutions..


http://chandoo.org/forums/topic/posting-a-sample-workbook
 
With unprotected sheet

Select all cells (twice - thrice Ctrl + a) right click format cells, protection, UNCHECK the locked option.

and the use the following macro in requried Worksheet (not module)

Replace password and range as requreid...


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.Unprotect "Password"


For Each cell In Range("A1:A500")

If cell.Value = "Yes" Then

cell.EntireRow.Locked = True

End If

Next cell


ActiveSheet.Protect "Password"


End Sub
 
Thanx Sir my concern is resolved by using:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


For Each cell In Range("Y3:Y500")

If cell.Value = "Yes" Then

ActiveSheet.Unprotect "Password"

cell.EntireRow.Locked = True

ActiveSheet.Protect "Password"

End If

Next cell


End Sub
 
Back
Top