|
How do I lock a cell in Excel after a drop down list entery
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 |
How do I lock a cell in Excel after a drop down list entery
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 |
How do I lock a cell in Excel after a drop down list entery
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? "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 |
How do I lock a cell in Excel after a drop down list entery
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 |
How do I lock a cell in Excel after a drop down list entery
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 |
How do I lock a cell in Excel after a drop down list entery
Gord,
Thank you, I added my range of cells as you recommended, and the form works perfect....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 |
How do I lock a cell in Excel after a drop down list entery
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 |
How do I lock a cell in Excel after a drop down list entery
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 |
How do I lock a cell in Excel after a drop down list entery
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 |
How do I lock a cell in Excel after a drop down list entery
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 |
| All times are GMT +1. The time now is 12:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com