How can I protect a range of cells(Ex. A3:A6) after I pick a value from a
listbox.

Chay

This assumes you have all cells unlocked and sheet is protected with the
password "justme"

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B6")) Is Nothing Then
ActiveSheet.Unprotect Password:="justme"
With Target
If .Value = 3 Then
Range("A3:A6").Cells.Locked = True
End If
End With
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

This is event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module.

Change Value = 3 to whatever value you will get from dropdown in B6


Gord Dibben MS Excel MVP




On Thu, 30 Nov 2006 00:07:00 -0800, Chay wrote:

How can I protect a range of cells(Ex. A3:A6) after I pick a value from a
listbox.

Thank you Gord Gibben for your code. Both of you guys helped me a lot. You
are pure geniuses. Thanks a million!!!!!!

"Gord Dibben" wrote:

Chay

This assumes you have all cells unlocked and sheet is protected with the
password "justme"

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B6")) Is Nothing Then
ActiveSheet.Unprotect Password:="justme"
With Target
If .Value = 3 Then
Range("A3:A6").Cells.Locked = True
End If
End With
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

This is event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module.

Change Value = 3 to whatever value you will get from dropdown in B6


Gord Dibben MS Excel MVP




On Thu, 30 Nov 2006 00:07:00 -0800, Chay wrote:

How can I protect a range of cells(Ex. A3:A6) after I pick a value from a
listbox.