Lock row

R

rodw

Can i have a cell at the end of a row that when selected it will loc
the complete row so it can not be edite
 
R

Ron de Bruin

I am not sure that I know what you want to do
Do you want to protect the cells in the row only if you select a cell in that row one time?
 
R

rodw

i basically want a cell at the end of the row to have an option lock o
unlock, this can be a drop down or toggle button

but when the cells value is locked then i dont want any of the othe
cells in that row to be able to be edite
 
R

Ron de Bruin

Try this on a new worksheet

First select all the cells in the worksheet
Format>Cells..Protection
Uncheck locked

Copy this event in the worksheet module
If you enter a 1 in the H column the cells in A:G are locked
If you delete the 1 or enter something else you can use the cells

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Cells(Target.Row, "H"), Target) Is Nothing Then
If Target.Value = 1 Then
ActiveSheet.Unprotect
Range(Cells(Target.Row, "A"), Cells(Target.Row, "G")).Cells.Locked = True
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
Range(Cells(Target.Row, "A"), Cells(Target.Row, "G")).Cells.Locked = False
ActiveSheet.Protect
End If
End If
End Sub
 
D

Dave Peterson

Another version:

I'd set up my worksheet first.
Ctrl-a to select all the cells (ctrl-a twice in xl2003).
Then format|cells|protection Tab|Uncheck Locked)

So now all the cells are unlocked.

Then use this variation of Ron's code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Application.Intersect(Me.Range("H:H"), Target) Is Nothing Then Exit Sub
If IsEmpty(Target) Then Exit Sub

With Target
If .Locked = True Then
'then they shouldn't have been able to change this cell!
'so this shouldn't happen
Exit Sub
Else
Me.Unprotect Password:="hi"
.EntireRow.Cells.Locked = True
Me.Protect Password:="hi"
End If
End With
End Sub

This version locks the entirerow as soon as you type anything in column H. And
since H is locked on a protected sheet, you can't change that cell--so you don't
need to check it anymore.

That said, worksheet protection is very weak. There's code posted here every
day/week that would unprotect the worksheet.

shhhh:
From J.E. McGimpsey's site:
http://www.mcgimpsey.com/excel/removepwords.html
 
R

Ron de Bruin

Hi Dave
to have an option lock or unlock
Click to expand...

The OP want a option to lock or unlock
That's why my example that lock/unlock a few columns in the row
and not the whole row.
 
D

Dave Peterson

I read that follow up message a few times and each time, I read it incorrectly.

(you can have this one, too!!! <vbg>)

Ron de Bruin said:
Hi Dave
to have an option lock or unlock
Click to expand...

The OP want a option to lock or unlock
That's why my example that lock/unlock a few columns in the row
and not the whole row.
Click to expand...
 
Top