Lock specific rows with a drop down box

**ecraw** · 05-07-2014, 12:49 PM

I have a spreadsheet with data entered in columns A thru N. Columns are locked except for F,G,K,M, and N which data must be entered manually. In column O, I have a drop down box with "closed" in it. I want to lock the rest of the row (so if row in row 9, for example it would lock F9,G9,K9,M9, and N9) once the word "closed" is inserted with the drop down box. Anyone know how I could do this? I am currently using a macro but it doesn't work unless the sheet is unlocked. Thanks in Advance.

Macro currently used:

Private Sub Worksheet_Change(ByVal Target As Range)

'if more than 1 cell is changed or changed cell is not
'in column O, end macro (do nothing)
If Target.Cells.Count > 1 Or _
Intersect(Target, [O:O]) Is Nothing Then Exit Sub

'unprotect the sheet
ActiveSheet.Unprotect

'if value in column O is "Closed," lock all cells in that row
If Target = "Closed" Then Rows(Target.Row).Locked = True

'protect the sheet
ActiveSheet.Protect

End Sub

**Kenneth Hobson** · 05-07-2014, 01:04 PM

Welcome to the forum!

Change the password to suit. Right click the sheet's tab, View Code, and paste. I normally put the Protection code in thisworkbook's Open event so code can do this sort of thing.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, o As Range, v As Long
  Set o = Intersect(Target, Columns("O:O"))
  With Target
    If .Row = 1 Or .Cells.Count > 1 Or o Is Nothing Then Exit Sub
    ActiveSheet.Protect "ken", UserInterfaceOnly:=True
    v = .Row
    Set r = Range("F" & v & "," & "G" & v & "," & "K" & v & "," & "M" & v)
    r.Locked = False
    If LCase(.Value2) = "closed" Then r.Locked = True
  End With
End Sub

**ecraw** · 05-07-2014, 01:08 PM

Worked like a charm! Thank you so much.

Lock specific rows with a drop down box

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Lock specific rows with a drop down box

Re: Lock specific rows with a drop down box

Re: Lock specific rows with a drop down box

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Lock specific cells once a value is entered with a drop down list

Drop Down Menu: How to attach a behavior so that only certain specific rows are shown?

Excel 2007 : Lock/unlock cells containing drop down validation based results of another drop down

How to lock the 1st drop down list

Lock specific rows while inserting new rows

Bookmarks

Bookmarks

Posting Permissions