Lock & Unlock Cells Based On Validation
Jun 8, 2007
I need to lock certain cells on a worksheet when a value is selected from a validation list. For example:
user goes to select an option from a validation list, that cell is now locked.
User decides to change the value with a blank option, available on the same list. the cell becomes unlocked ready for new input from the list.
the cell can only allow input when the value in the list matches that of another cell. if another cell already has different data in, it wont allow you to change the value.
View 3 Replies
ADVERTISEMENT
Apr 24, 2008
I'm looking for a solution to a problem and from what I've read in the forums so far, this is the place to come. I first want to say that I have tried to solve the problem by using guidance from other similar threads and adapt those answers to my needs, but as my understanding of VBA is non-existent this was somewhat difficult. I don't want to post a repeat question and I hate wasting people's time if unnecessary, but I can't do this on my own.
That being said, here is the problem;
(I have attached a simplified copy of the worksheet I am working on for reference)
I would like to lock a particular cell in my spreadsheet based upon the selections made from two drop down validation lists (Cells “C4” & “D4”)
Essentially, if a correct choice is made in both, then the cell “E4” becomes unlocked for editing. If however, the correct selection is made in one list, but not the other, I need the cell “E4” to remain locked and a message to appear to prompt the user to correct their mistake.
Furthermore, both the validation lists have a whole range of selections that are very similar but have subtle differences;
E.g.
"Soft Eye by Talurit"
"Soft Eye by Splice"
Is it possible to write a VBA code that performs a similar function to the =IF(ISNUMBER(SEARCH("**",))) formula, so that if any choice that contains the word "Soft" is selected from either or both lists then the cell “E4” becomes unlocked, but if the selection does not contain that word then the cell remains locked?
If anyone could provide a code to make this happen, I would be very grateful! But please explain how to do this, as the word “Soft” won’t be the only parameter and I may need to alter the code so I need to understand the principle behind it; For example, on the spreadsheet, if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Soft Eye’ is selected then cell E4 is unlocked for editing. If in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Fused and Tapered’ is selected then cell E4 is unlocked for editing, but if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Standard Eye’ is selected then cell E4 remains locked and an error message appears to alert the user that his/her choices are incorrect. If however, "Soft" is not selected in either list then cell E4 remains locked (it has to be selected in at least one of the two lists to allow editing).
This is a work in progress (not all the formulas that will eventually be used have been entered yet, so please excuse the untidiness of it all) and the choices are likely to change based on input from my colleagues, so I need to understand how the VBA works in order to alter it accordingly, but I don’t want to put any more work into this (it’s still in the early stages) if it isn’t possible to achieve these needs.
View 9 Replies
View Related
Dec 20, 2007
trying to put together a formula, I am trying enable two cells to change between being 'Locked' and 'Unlocked'. I am doing this by using a CheckBox which is referenced to cell [E16] so that when it is checked, 'TRUE' will be displayed and when Unchecked 'FALSE' is displayed. From this I have tried to devise a formula for the cells [c26:I26] and [K26] that when cell [E16] shows 'TRUE' the cells [c26:I26] and [K26] are Unlocked and when it shows 'FALSE' the cells [c26:I26] and [K26] then become locked.
View 3 Replies
View Related
Feb 25, 2009
I want cell g12 to be locked unless cell h7 is Grass Fire or Timber Fire (H7 is a drop down validated cell)
View 8 Replies
View Related
May 15, 2013
I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.
I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!
View 1 Replies
View Related
Nov 6, 2006
I need to lock cells or ranges in a worksheet which has value (any value)....
The cells which are blank should be unlocked so that the users can enter data.
View 5 Replies
View Related
Sep 12, 2013
How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies
View Related
Jan 14, 2013
I'm trying to write a vba code that does the following....
There is a question in column A to which the user chooses yes or no from column B. Based on the response in column B, I'd like the same row column D to be formatted so that if the anser is yes, the cell is white, and unlocked. If the response is no, the cell is cleared, locked and the greyed out. (e.g. if B4 is "Yes", the format in D4 will change) So far I've come up with the following which formats the colour:
Dim response As Range
For Each response In Range("$C$10:$C$73")
If response.Value = "Yes" Then
response.Select
ActiveCell.Offset(0, 2).Range("A1").Select
With Selection.Interior
[Code] .......
How to add in a .clearcontents function, so that the contents are cleared if the response is not "yes", and also what I would need to add to unlock the cell in column D?
View 1 Replies
View Related
Aug 7, 2007
Is it possible to lock/unlock the VBA code in a workbook through VBA from another workbook? I wish to copy modules from one workbook to another, both workbooks will have the VBA code password protected to stop prying eyes. Obviously I need to unlock the VBA in the target workbook before the copy and lock it again afterwards. Can this be done programatically? If there is no function in VBA to do this can you provide an example of how to do it using SendKeys if that is the only way to do it?
View 3 Replies
View Related
Sep 13, 2012
I have the unlock/lock code below but i cannot get it to unlock the correct spreadsheet. Where do i need to place it in the code so that it will work properly.
Code to unlock:
Public Sub unlocksheet()
'unlock the worksheet
On Error Resume Next
ActiveSheet.Unprotect Password:="test"
Within this code:
Private Sub cmdSub_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("AFW")
'find first empty row in database
[Code] .........
View 1 Replies
View Related
Dec 10, 2006
None of the information Ive found on the forums seem to work in my sheet and Im not sure why. After protecting my sheet/workbook, I would like a particular input of a cell to Unlock another cell.
ie if the contents of cell A1 is the phrase "TRUE", then cell B1 will be Unlocked for editing. If the contents of A1 is "FALSE", then B1 shall remain locked. From what i can tell, this must be carried out using VBA code. If anyone can resolve this issue, Id be also very grateful if you could explain what each line of the code means
View 6 Replies
View Related
May 29, 2012
Is there a way to lock and unlock multiple excel spread sheets with only one password at one go? For example if I have 5 sheets, I would like to be able to lock and unlock Sheets 2-5 with one password at one go while leaving sheet 1 unprotected. Do not want to type a pwd for each sheet.
View 9 Replies
View Related
Oct 16, 2009
I have a worksheet that, unless a form for data entry is activated, should be read-only for columns 'A' thru 'M' and 'O' with column 'N' left editable.
The form is activated by a button with a macro assigned to enter the data in the above columns when the form is closed. This has been compiled and works nicely with the sheet unlocked, however, as i need certain columns to be read-only unless the form is activated, i'm stuck with leaving the whole sheet unlocked for editing with or without the form being activated.
Is there any way a VBA code could be used to unlock the worksheet columns that are read-only when the form is activated, and then lock them once the form data is entered to the worksheet and the form closed?
View 9 Replies
View Related
Jun 26, 2014
I want to make break tracker. When i press Windows + L Or CTRL + ALT + DELETE (Lock computer) Automatically lock time upload in my excel sheet. Then when i unlock pc then automatically unlock time will upload in my excel sheet.
View 1 Replies
View Related
Oct 1, 2009
I need a macro which will lock the cells after data is entered in the cell once. Cell should should be only edited after a password is provided. I found the following macro but the problem is that it's not locking the cells where I have Data Validation List :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:M10000")) Is Nothing Then 'set your range here
ActiveSheet.Unprotect Password:="1234"
Target.Locked = True
ActiveSheet.Protect Password:="1234"
End If
End Sub
View 9 Replies
View Related
Apr 22, 2009
I'm using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.
It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).
Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?
View 7 Replies
View Related
Apr 18, 2008
I am trying to lock the unused cells in 32, 2 column by 7 row named ranges, based on whether or not two cells, above each range are equal or less than each other. In other words while one of the cells is less than or equal to the second cell all cells in the range below should be unlocked, as soon as that condition is no longer true the blank cells need to be locked.
I am trying to use this in the Workbook_Sheetcalculate so that the macro will run automatically.
View 3 Replies
View Related
Oct 31, 2006
I had posted a similar question on 10/27 but the thread has mysteriously diappeared. So, I am reposting my question. What I am trying to do is probably easily done using VBA. See attached workbook example.xls to follow along.
I have a dropdown (YES/NO) selection in Column C. I want to do the following when 'YES' is the selection for any row:
1) do a vlookup into the 'data' tab and return corresponding value in Column D
2) Lock the cell after the value is returned into Column D
If the selection is 'NO' then do nothing., ie., leave the cell blank and unlocked.
View 2 Replies
View Related
Mar 30, 2007
I have a spreadsheet where there are two columns for the user to enter data in, but they can only select one column. I want to lock the second cell if data is entered in the first. I can't find a way from validation to do this. Is there another way via VBA (stupid question, I know... VBA does everything but scratch your butt)
View 4 Replies
View Related
Oct 26, 2013
Trying to incorporate the following in a worksheet:
Lock the cells automatically in the columns that are older than 2 days or more
Lock the cells corresponding to today...
View 2 Replies
View Related
Oct 25, 2013
Trying to incorporate the following in a worksheet: Lock the cells automatically in the columns that are older than 2 days or more Lock the cells corresponding to today and yesterday using a command button after entering X Copy the sheet multiple times with the same features
View 14 Replies
View Related
Aug 29, 2007
I have created a spreadsheet which I am trying to set up to allow users to select only 1 cell in a range and lock the rest. Each cell D5 to I5 has an assigned number 0 to 5. What I am trying to achieve is when a user clicks on a cell, the value of that cell is displayed and the remaining cells are locked. e.g. if the user selects G6, 3 is displayed and cells D6,E6,F6 and I6 are locked. The code which I am using is giving strange and unpredictable results.
Private Sub Worksheet_Change(ByVal Target As Range)
If [D5] = "0" Then
ActiveSheet.Unprotect ("PASSWORD")
[I5].Locked = True
ActiveSheet.Protect ("PASSWORD")
'Remove locked property if D5's value is anything else or is deleted.
Else
ActiveSheet.Unprotect ("PASSWORD")
[I5].Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If............................
View 2 Replies
View Related
Apr 4, 2009
Having a problem locking the sheet and still have it work. Not sure if their is a easy work around for this. Just lock sheet and see what I get.
View 7 Replies
View Related
Jun 6, 2013
1- If I wish to write in the cell I can do it ?
2- When I do not wish to write in the cell I can do it (Its meaning protect the cell.)
View 2 Replies
View Related
Feb 26, 2009
i have a query spreadsheet where all business queries are logged. Next to each query you need to select a resolving reason from a drop down list, however i would like some of these to be locked so only certain people could use them.
Lets for ease of use the resolving reasons are the following -
Not paid
Allocated Correctly
Rejected
And say the drop down list is in column P
I would like everyone to use the top 2 but only certain members of the team to be able to use the last one.
I was thinking protect the cells in some way and a worksheet change event of some kind so protect / unprotect depending on the resolving reason.
View 9 Replies
View Related
Apr 22, 2009
What I'm trying to achieve is to be able to lock the text length of a column so that users can enter text data into that column but not exceed the allowable length for our purposes. I've found a way to use auto loading macros to make a popup if they exceed the set length.
However, that is useless for our purposes because 1) pasting data into the cells as opposed to manually entering data overrides the validation just as it does when you set it with the data validation menus, 2) it requires that macros be activated in the end user's computer which we cannot guarantee will happen. If you lock in the data validation by protecting the worksheet, the end user can't enter data. Do you know of a way to lock in the data validation without using active controls like macros, and yet allow the end user to enter data into the cells. (Perhaps a solution would be to somehow prevent the pasting of data into a cell forcing the data validation to be utilized?)
View 3 Replies
View Related
Nov 8, 2013
After going through some posts in this site, I found that, we can use custom validation for locking a cells when the condition is not met.I have tried using that, But I am not able to get it correctly.I have attached the sheet for your ref.
What I want is, when I enter a month in A2 (Which will always be the 1st of that month), that particular column should get unlocked/should be able to edit that month's column only. I have just provided on table here, but what I want is for entire column so that, I can use the same formula for a larger sheet
View 3 Replies
View Related
Jul 4, 2007
Is it possible to unlock Blank or Empty cells within a Range?
" for example; all cells in the range (A1:F10) > 0 .locked = False "
I have some code from something simmilar i have added below:
Sub test_Click()
Dim rngTemp As Range
For Each rngTemp In Range("grey").Cells
If len(rngtemp.value) > 0 Then
LockMatchedCells rngTemp, Range("against")
endif
Next
End Sub
View 9 Replies
View Related
Jan 19, 2012
Any way to conditionally unlock and unprotect certain cells in a column. I have been unsuccessful with my attempts at customizing other people's vba code that is a little different than what I am trying to achieve. So here's what I would like to accomplish:
If A23 = "Please See DME", then unprotect (pw=richie13) and unlock D23, then protect (pw=richie13) sheet again
If A24 = "Please See DME", then unprotect (pw=richie13) and unlock D24, then protect (pw=richie13) sheet again
If A25 = "Please See DME", then unprotect (pw=richie13) and unlock D25, then protect (pw=richie13) sheet again
and so on and so on.
View 9 Replies
View Related
Jan 15, 2014
I want to select the rows that i want to copy then I would like a command button to unlock the sheet using password "2014", copy & paste my selection in the last empty row, and then lock the sheet.
View 9 Replies
View Related