Conditional Cell Lock Unlock

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


ADVERTISEMENT

Lock Used Cell. Unlock Blank Cells

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

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

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 / 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

Programmatically Lock/unlock Vba

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

Lock / Unlock Spreadsheet Userform

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

Lock And Unlock Multiple Excel Spreadsheets?

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

Use VBA To Unlock/lock A Worksheet For Data Entry?

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

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

Track Computer Lock And Unlock Time In Excel?

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

Lock/Unlock Cells Based On Criteria From Validation Lists

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

Conditional Multiple Column Unlock Via Cell Reference?

Aug 2, 2013

I have an inventory log that requires multiple cells in different columns to be unlocked based on a reference cell's input.

So, if a cell in column E has "MORNING" entered then cells L/M/N are unlocked and said user can input data for that row, and only that row. If anything else is in E, then L/M/N are left locked.

Is there a way to do this without coding, just using regular IF() in the cell directly; IF(ISTEXT(E3)=MORNING, Unlock, KeepLock)? I know that's nowhere close to being a legitimate statement, but it's the best way I can translate my thoughts.

View 9 Replies View Related

Excel 2007 :: Lock / Unlock Cells Based On Values Selected In Dropdown List

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

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

Conditional Formatting Formula Lock?

Apr 29, 2014

I have a sheet which contains conditional formatting formulas.How i can Lock or protect the forumluas to view others.

View 2 Replies View Related

Conditional Macro - Insert Text And Lock All Duplicate Cells Except For First One

Sep 16, 2012

I am new to VBA but I am trying to insert text and lock all duplicate cells except for the first one. Below is the macro I am trying to make. I keep getting an error on the line:

If(COUNTIF($A$9:$A$76,A9)=1,FALSE,NOT(COUNTIF($A$9:A9,A9)=1))

This is my macro:

Sub SetVoters()
'
' SetVoters Macro
'

'
Sub SetVoters()
'
' SetVoters Macro
'

'
ActiveSheet.Unprotect

[Code] ......

View 1 Replies View Related

Unlock Cell Using Code

Mar 6, 2009

code that will unlock cell g12 and af9 when cell e9 equals "WF-"

Basically I need it to say (I really don't know how to write code)
If "E9"="WF-" unlock "G12" and "AF12"
If not, clear contents of "G12" and "AF12"

View 11 Replies View Related

Unlock A Cell With A Button

Mar 19, 2009

I want to let the user unlock certain cells but only after they have clicked a button. To notify that the cell is unlocked I also want it to change colour.
Is this possible?

I have attached a small example. Password is mg

unlock.xlsx

View 6 Replies View Related

Unlock Sheet On Cell Selection?

Jul 4, 2014

I have a table and need to protect the sheet. From searching I see its a common problem as the table wont auto expand on a protected sheet.

I was thinking maybe I could create some sort of change event so if a user selects cells on Column A,B,G,H rows 13-1000 the sheet will auto unlock then lock again once they leave those cells.

I think it may need a few seconds delay to give table chance to autoexpand before sheet is locked again.

View 3 Replies View Related

Unlock Cell Range If Row Is Blank

Nov 9, 2012

I have a worksheet with active range A9 thru K200 that is locked. When worksheet is opened, I need it to automatically unlock all rows that are blank, for users to input data. It would be great if this could also require data in column A, C and K before allowing workbook to be saved and closed.

View 2 Replies View Related

Unlock Cell/s Dependant Upon Entry In Another Cell

Jan 3, 2010

I've never dealt with vba before and I'm not even sure if that's correct. I wish to lock a worksheet with two cells left unlocked, when an entry is made in one or other of these two cells I need other cells to become unlocked.

View 2 Replies View Related

VBA Code To Unlock Cell If Another Cell Value Is Greater Than 6.00

Feb 28, 2013

I have a protected worksheet with most cells locked and some that are unlocked. I also know the password to unlock the sheet. VBA code to monitor a cell(B29 in my case) and if it has a value of 6.00 or more than it will unlock cell B34?

View 7 Replies View Related

Excel 2010 :: Unlock Whole Workbook By Putting Password In A Cell

Jul 11, 2014

I'd like to be able to unlock the whole of a workbook by putting the password into a specific cell and then the whole thing locks again when it is saved. I have been able to find the coding to do this for a worksheet but I'd really like to be able to unlock the whole project.

View 7 Replies View Related

Unlock A Cell Acording To Another Cell

Aug 10, 2006

I am trying to make a sheet that is protected. It's protected in that way that all cells are protected and locked except cell C3. When someone then type anything in cell C3 and leave the cell then cell D3 automatic unlock. I know how to do it with a macro. But I don't want that the user have to press the macro-buttom to unlock cell D3. It should unlock cell D3 automatic.

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

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







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