How do I lock a cell in Excel after a drop down list entery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Thanx Barb,
I am rather new at Excel, is this solution a MACRO, or can I enter it in the
"Code" sheet for the worksheet tab?
 
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
 
Casey

Before you implement this code you must first select all cells on the sheet and
Unlock them via Format>Cells>Protection.

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
 
Gord,
Thank you, I added my range of cells as you recommended, and the form works
perfect....Casey
 
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
 
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
 
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
 
Happy to hear.

Just so you know, a user can always go to Tools>Protection 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
 
Back
Top