Lock Cell Based On Value In Another Cell

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


ADVERTISEMENT

Lock / Unlock Excel Cell Based On Contents Of Another Cell?

Sep 12, 2013

How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?

View 1 Replies View Related

VBA To Clear And Lock / Unlock Cell Based On Another Cell

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

Lock Cell Based On Value Of Adjacent Cell?

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

Lock Cell Range Based On Cell Value?

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

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 View Related

Lock Current Row Based On Cell Value

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

Lock Row Based On Cell Contents

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

Lock Cell Based Upon Formula

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

Lock & Unlock A Cell Based On Another Cells Value

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

Conditionally Lock Row Based On One Cell's Contents

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

Lock/unlock Cell Based On Another Cells Input

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

Lock Cell After Data Entered And Have Moved Off That Cell

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

Lock Format In Cell - Even If Copy And Pasting To That Cell

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

Using Checkbox To Change Cell Value And Lock Cell

Aug 18, 2014

I need a checkbox (actually a few but I will use the same code and change the name and cell reference) to do the following

Checkbox in A1 is ON by defaultB1 is 100User can free change the value of B1 when checkbox is onWhen checkbox is OFF"100" (or whatever number entered by the user) gets storedB1 changes to a relatively large number "9999"B1 changes cell color to grey and is locked for editWhen checkbox is ON againThe value stored previous to checkbox turned OFF is restored back to B1B1 changes back to normal cell color (no fill or white)B1 is unlocked and user can edit the cell

View 5 Replies View Related

Lock A Cell Value

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

Lock Cell Value

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

Cell Lock

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

Conditional Cell Lock

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

VBA To Auto Lock A Cell

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

Can Lock Set Of Cell To Only Allow 30 Characters Or Less

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

How To Lock A Particualr Cell Having Value

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

Lock Cell After Entry

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

Lock Cell After Selection

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

Lock Cell Contain Formula But Allow To Insert Row?

Nov 8, 2013

i am having problem with doing team, i would like to lock cell contain formulas but allow users insert row.

View 2 Replies View Related

Automatic Cell Lock After Typing?

Nov 23, 2012

I want to learn how i can able to do a function or ... to LOCK a Cell automatically after editing or typing and no one able to edit it again without entering Password.

View 14 Replies View Related

Lock Data In Cell After Entering Value?

Feb 2, 2014

0: i have a sheet 1 protected and few edit range (Col A,B,C,D,E and F).

1: I have a validation on multiple adjacent columns (like Col A, Col C, Col E).
2: I need a code where if i select value in col a (cell A2) cell B2 should get current date and Time and both the cells (A2 and B2) should be locked. when i select data in cell C2 , D2 should get current date and time(Cell C2 and D2 should be protected).
3: User can enter data in A3 and B3 will get current date and Time (now A3 and B3) should be locked.
4: I need a function where user can call it and unlock the locked cells and edit the data and re-lock the cells again.

apart from the Col A,B,C,D,E and F, other columns and cells remain locked.

View 2 Replies View Related

How To Lock Certain Cells If Another Cell Is Blank

Mar 8, 2014

i have a situation where i want cells c13:e13 to be locked if cell g5 is blank. and same cells will be unlocked only if g5 contains text only.

View 4 Replies View Related

Lock Cell Reference In Formulas

Nov 30, 2009

In the attached example, I need to create a formula for cell I2 (Balance Remaining) that is driven off the most recent calculation (in this case, F68) but still allows for additional rows to be added to the ongoing list of orders. I believe I'm supposed to use an index formula but not sure how to write/apply it? I need to be able to add rows past 68 and still have Cell I2 (Balance Remaining) continue to calculate correctly.

View 10 Replies View Related

How To Lock Cell Once Data Has Been Inputted

Sep 16, 2011

Is it possible to lock a cell once data has been inputted into it??

I want to make a spreadsheet which has empty cells for reference numbers that once entered cannot be changed unless it is by the administrator.

i also want the spreadsheet to be accessed on multiple pc's by multiple users.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved