protect and set password if a cell is not empty

[mark692]

	A	B	C	D	E
1	Date	Detergent	Out	In
2		Tide		3
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

hi guys is this possible, i want a row from A to D protected and set a password if column "IN" is not empty? is that possible? so no one can edit the row unless he knows the password?

 

[mumps]

Do you want the row to be protected immediately after the cell in column D is populated? Also, do you want the entire row protected or just from A to D?

 

[mark692]

Do you want the row to be protected immediately after the cell in column D is populated? Also, do you want the entire row protected or just from A to D?

Hi sir its either if the D is populated then A to D will automatically protected or a button to set A to D protected if D is not empty,

i want the row from A to D to be protected if the D cell is not empty sir

 

[mumps]

First unlock all the cells in columns A to D. Then protect the sheet with a password of your choosing. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the password in the code (highlighted in red) to your password. Close the code window to return to your sheet. Enter a value in column D and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="[COLOR="#FF0000"]MyPassword[/COLOR]"
    If Target <> "" Then
        Range("A" & Target.Row).Resize(1, 4).Locked = True
    Else
        Range("A" & Target.Row).Resize(1, 4).Locked = False
    End If
    ActiveSheet.Protect Password:="[COLOR="#FF0000"]MyPassword[/COLOR]"
    ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

 

[mark692]

First unlock all the cells in columns A to D. Then protect the sheet with a password of your choosing. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the password in the code (highlighted in red) to your password. Close the code window to return to your sheet. Enter a value in column D and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="[COLOR=#FF0000]MyPassword[/COLOR]"
    If Target <> "" Then
        Range("A" & Target.Row).Resize(1, 4).Locked = True
    Else
        Range("A" & Target.Row).Resize(1, 4).Locked = False
    End If
    ActiveSheet.Protect Password:="[COLOR=#FF0000]MyPassword[/COLOR]"
    ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

thank you sir i will try this

 

[mark692]

hello sir im here again i tried the code but i have a problem row that will be locked on that code is a part of a table, when i use your code the row i want to be locked is protected but the problem is i cant use the function sort and filter of the table, i hope you can help me with this thanks!

 

[mumps]

Have a look at this link: https://www.extendoffice.com/documents/excel/4673-excel-sort-filter-protected-sheet.html#a1

 

[mark692]

thank you sir for your answer is there a way or code that i will add on this code to allow sort?

Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect Password:="MyPassword"
If Target <> "" Then
Range("A" & Target.Row).Resize(1, 4).Locked = True
Else
Range("A" & Target.Row).Resize(1, 4).Locked = False
End If
ActiveSheet.Protect Password:="MyPassword"
ActiveSheet.EnableSelection = xlUnlockedCells End Sub

 

[mumps]

If you follow the instructions at the link I provided, you don't have to add anything to the code.

 

[mark692]

If you follow the instructions at the link I provided, you don't have to add anything to the code.

when i fallow the instruction the i still need to set password to protect it, i need is to automatically protect the range when i input a data, because there will be other user that will use the file, i dont want them to set the PW, this code works but it doesnt let me Sort the range

Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect Password:="MyPassword"
If Target <> "" Then
Range("A" & Target.Row).Resize(1, 4).Locked = True
Else
Range("A" & Target.Row).Resize(1, 4).Locked = False
End If
ActiveSheet.Protect Password:="MyPassword"
ActiveSheet.EnableSelection = xlUnlockedCells End Sub