 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / September 2007How 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 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.  SignatureHTH, Barb Reinhardt > 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 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? > 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 [quoted text clipped - 30 lines] > > made. This is to prevent any changes by others who will have access to the > > spreadsheet. Any suggestions would be helpful....Thanx....Casey 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 > 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 [quoted text clipped - 30 lines] > > made. This is to prevent any changes by others who will have access to the > > spreadsheet. Any suggestions would be helpful....Thanx....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 >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 [quoted text clipped - 34 lines] >> > made. This is to prevent any changes by others who will have access to the >> > spreadsheet. Any suggestions would be helpful....Thanx....Casey Gord, Thank you, I added my range of cells as you recommended, and the form works perfect....Casey > Casey > [quoted text clipped - 71 lines] > >> > made. This is to prevent any changes by others who will have access to the > >> > spreadsheet. Any suggestions would be helpful....Thanx....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 > Casey > [quoted text clipped - 71 lines] > >> > made. This is to prevent any changes by others who will have access to the > >> > spreadsheet. Any suggestions would be helpful....Thanx....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 >Well Gord, >I do not know where I went wrong. I reopened my spreadsheet, to show a co [quoted text clipped - 80 lines] >> >> > made. This is to prevent any changes by others who will have access to the >> >> > spreadsheet. Any suggestions would be helpful....Thanx....Casey 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 > I don't know what to say. > [quoted text clipped - 93 lines] > >> >> > made. This is to prevent any changes by others who will have access to the > >> >> > spreadsheet. Any suggestions would be helpful....Thanx....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 >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 [quoted text clipped - 96 lines] >> >> >> > made. This is to prevent any changes by others who will have access to the >> >> >> > spreadsheet. Any suggestions would be helpful....Thanx....Casey |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.