How To Lock Cell Based On First Value
Jul 2, 2014
I have a multisheet excel workbook that I am programming. I was wondering if there is a way to lock a cell on one sheet after the initial value was put in, allowing me to change it later. This may not be so clear so here is the problem....
I have Workbook1. The input from cell A3 on Sheet1 is 5. I have made a second sheet called Order Form. In cell B5 of Order form I have made it ='Sheet1'!A3. I am looking for a way to be able to change cell A3 on Sheet1, but keep the 5 on Order Form.
View 1 Replies
ADVERTISEMENT
Jul 16, 2012
I need to have a current row password protected when the value in ActiveCell.Offset(0, 0).Value = "Close"
but just to lock that current row. What I have is a close button on a userform that changes the ActiveCell.Offset(0, 0).Value = "Close", i just need to add another line of code to lock the row.
VB: ActiveCell.Offset(0, 0).Value = "Close" (And lock current row With password 123)
View 2 Replies
View Related
Oct 17, 2008
I wish to be able to lock an entire row after an entry has been made in a certain cell.
e.g. let's say i want to lock row 9 in its' entirety following entry of "Yes" in cell G9. If there is no entry in G9 the the entire row should be still unlocked. Likewise with any row with a corresponding entry in column G.
View 14 Replies
View Related
Apr 10, 2007
Is it possible to lock all cells with the exception of a select few(say a column) based upon a date? I have a spreadsheet where I want to lock all cells with the exception of the current months information. I want the spreadsheet to auto unlock the date that is listed on the top of the spreadsheet. I have columns for all months of the yea and I have the current month listed in cell A1. I want match the A1 cell to the column header to unlock the correct column.
View 4 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 6, 2008
I'm trying to figure out how to lock a range of cells (all in a row) when some puts a specific character in one of the cells. I have a sheet where I want people to enter data. Once they put a 'C' in the last column to indicate that the entry is complete, the whole row should be locked so they can't change any of the cells for that entry. There are some good pointers already on the forum to help me get the basics:
Conditional Cell Lock Unlock
http://www.ozgrid.com/forum/showthread.php?t=66397
http://www.ozgrid.com/forum/showthread.php?t=76379
However, all of these point to specific cells rather than a range of cells that can be located close to the target of the change.
View 3 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
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
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
Sep 20, 2013
I need to lock the cells in a certain column based on the value of the right side cell. If the value of the right side cell is "Slave" it should lock the cell, otherwise it should remain unlock. I read many codes in the internet but none of them worked properly for me.
View 9 Replies
View Related
Oct 23, 2012
I have a spreadsheet that has number between 1-20 entered in every cell in column B all the way down to row 15,000
I need to program something in VBA that will look at each number and then lock a certain range in that row based on that value in column B
For example:
In B3 there is the number '5' therefore I would want it to stop the user entering anything between C3 and G3
In B4 there is the number '3' therefore I would want it to stop the user entering anything between C4 and E4
In B12500 there is the number '14' therefore I would want it to stop the user entering anything between C12500 and P12500
I don't want to have to type out programming for each criteria from 1-20 for 15,000 rows!
View 3 Replies
View Related
Apr 9, 2014
Purpose of the worksheet:
Different users can access my excelsheet to make a selection out of a drop down list, all other cells are locked and the worksheet is protected. There is an amount of "working shifts" all in row D one under another, the users can link people to that "working shift" based on the people that are in a drop down list (data validation).
So shift in D5 will be attributed to the person selected in E5; D6 to E6; ... This happens in multiple sheets in the workbook.
Problem:
Once I verified the link of the person to the "workshift", I put "OK" in cell F5 (not directly, but through a VLOOKUP that searches in a summarysheet). As soon as "OK" is in cell F5, I want cell E5 to be locked. But also for F6 and E6 ... (range: E5:E250).
This way, no one can change the person that I verifed (nor is the person changed without me noticing it).
View 4 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
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
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
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
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
Jul 25, 2007
=IF(A4='Exported Data'!B2,'Exported Data'!C2,"")
How can I lock A so when I copy over to the right the formula doesn't change that reference to B4?
Is there a way to have the True value (Exported Data'!C2) reference one cell over instead of selecting the cell to reference from?
View 9 Replies
View Related
Jan 12, 2008
Need code that would make cell J2 be populated with U no matter what was placed in cell J2 either manually or externally.
View 9 Replies
View Related
Feb 24, 2004
I can have amacro which will lock each cell where a "Y" or "N" is inserted........ eg the netire sheet is unprotected but the moment Y is inserted in C 5 the cell gets locked, next entry in D 10 has a N entry and that too upon entry get's locked
The idea being once Y or N is typed that cell can no longer be changed
View 9 Replies
View Related
Jan 5, 2013
Currently have a column of cells that after the data has been entered we would like the cell to lock to prevent changes. The sheet is currently protected with the cells in question unlocked (D173:D250). We would like to be able to change the data if we unprotect the sheet. We would also like to be able to add/delete cells (list) in the code as needed.
View 9 Replies
View Related
Aug 17, 2006
Is it possible to lock in the formatting that would require 12 digits even if someone was copying and pasting to the cell?
We are looking to make our users add information into cells in the format 0-00000-00000-0. They are often copy and pasting, thus overwritting the custom format we have in those cells.
I've looked at validation, but custom only works for a formula.
View 9 Replies
View Related
May 29, 2008
I have an sheet(see attached) with some dummy data, now i want to lock (with password) only those cells depending on the dropdown (highlighted in sheet).For eg,if we select A from dropdown of col c and then locked from dropdown of col D , it should lock the complete row(s) of value A in col H and rows with value B shall remain editable.
View 14 Replies
View Related
Apr 18, 2012
I have a range of cells I want to become locked (thus, uneditable), based upon the value of another cell.
For example, if cell A1 is "True," (based on a drop-down, not a formula) I want cells B1:B10 to become locked. I want the user to be able to edit these cells only when A1 is false. Also, the sheet is already passsword protected.
View 2 Replies
View Related
Jun 26, 2014
Is there a way I can lock a set of cell to only allow 30 characters or less and if so if they copy and paste they can go over 30 characters in a cell. I can not allow they to go over 30 characters.
View 2 Replies
View Related
Jul 3, 2006
how to lock a particular column having the particular value for all the sheets in a single work book thru macro code?
View 9 Replies
View Related
Mar 14, 2007
I am using the code below to prompt the user to enter his/her name in cell B4. What I would like to do is have it so that after the user enters his/her name, that cell is locked and cannot be cleared.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$4" Then
Range("B4").Value = InputBox("Please Enter " + Range("a4"), "Company")
End If
End Sub
View 9 Replies
View Related
May 8, 2007
Is it possible to lock the Cell after selecting the answer from list of that cell?
mean disable that cell from editing, deleting, and selecting another answer
View 2 Replies
View Related
