Happy to hear.

Just so you know, a user can always go to ToolsProtection and unprotect the
sheet and make a change in a DV dropdown.

Even if you password protect the sheet users can break sheet protection
passwords easily.


Gord

On Mon, 24 Sep 2007 14:32:02 -0700, Casey
wrote:

My mistake, I took a close look at what I did, and made minor correction, all
is good now, Thanx for all your help....Casey
"Gord Dibben" wrote:

I don't know what to say.

The sheet I tested on before posting works fine.

I saved, closed and reopened and OK.

I can't get to it until tomorrow A.M. but if you send the file to my email, not
the news group, I will have a look.

Change the AT and DOT to get my email address.


Gord

On Sun, 23 Sep 2007 19:04:01 -0700, Casey
wrote:

Well Gord,
I do not know where I went wrong. I reopened my spreadsheet, to show a co
worker how it functions, and now it does not work as it did befor I closed
it. I cleared all code, re entered the code you gave me, with my range
modifications, and it works fine for the first cell, but then locks up the
rest of the range as it did when I used Barb's code.
As I said I am new to Excel, and to be truthful, I am a user, not a builder.
Any help will be wonderful......Casey

"Gord Dibben" wrote:

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