Casey

Before you implement this code you must first select all cells on the sheet and
Unlock them via FormatCellsProtection.

I would also make some changes to Barb's code so it doen't trigger on every cell
on the sheet.

Add the cells with the DV dropdowns to MY_RANGE in the following.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myResponse As String
Const MY_RANGE As String = "A1:A10" ' "A1,C2,D4,E8" if not contiguous range
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Me.Range(MY_RANGE)) Is Nothing Then
On Error GoTo endit:
Application.EnableEvents = False
Do

myResponse = InputBox("Are you sure your entry is correct? Enter Y or N.")

Loop While LCase(myResponse) < "y" And LCase(myResponse) < "n"

If LCase(myResponse) = "n" Then GoTo endit:

Target.Parent.Unprotect

Target.Locked = True

Target.Parent.Protect
End If
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Sun, 23 Sep 2007 16:46:02 -0700, Casey
wrote:

I copied your suggestion and placed it in the worksheet view code page, and
it did work, for the first cell I entered, however it also locked all other
cells on the page, where did I go wrong.....Casey

"Barb Reinhardt" wrote:

You could use a variation of this. One way (thought probably not foolproof)
is to change the locked property of the cell from FALSE to TRUE when the cell
is


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myResponse As String

If Target.Count 1 Then Exit Sub

Do

myResponse = InputBox("Are you sure your entry is correct? Enter Y or
N.")

Loop While LCase(myResponse) < "y" And LCase(myResponse) < "n"

If LCase(myResponse) = "n" Then Exit Sub

Target.Parent.Unprotect

Target.Locked = True

Target.Parent.Protect


End Sub

Every time there's an entry, the user will need to ensure that the value is
correct before the cell is locked. I don't like it, but it's a starting
point.

--
HTH,
Barb Reinhardt



"Casey" wrote:

I have created a spreadsheet in which I have several "drop down lists", I
would like to lock the cell once a selection from the drop down list has been
made. This is to prevent any changes by others who will have access to the
spreadsheet. Any suggestions would be helpful....Thanx....Casey