Locking One Cell If Another Had A Value In It (conditional Locking)

Oct 10, 2008

I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.

View 9 Replies


ADVERTISEMENT

Locking Different Cell Ranges Then Locking Whole Sheet With Macros

Jan 4, 2010

I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.

The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.

What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.

I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.

I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....

If anyone has any suggestions I would be eternaly greatful.

Passwords on the sheet for the different buttons are abc or cobra.

You can download my Spreadsheet here.

[url]

View 9 Replies View Related

Conditional Cell Locking?

Mar 8, 2013

Is it possible to have a formula in a cell, based on the value in another cell, but if the other cell value is not true then allow for data entry in the cell

So
J10 executes a formula if the value in I10 (a list field) is 'Cash and Shares' =IF(I10="Cash & Share",ROUND(IF(AND(K10<>0,O10>0),K10/O10,0),0)).

However if the value of I10 is anything other than 'Cash and Shares' then do not execute the formula in J10 but allow users to enter another value.

Note: the cell needs to be locked. The worksheet will be protected and will allow users to Select Locked Cells and Unlocked Cells.

View 3 Replies View Related

Conditional Cell Locking Based On Font Color

May 26, 2009

Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?

More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?

View 9 Replies View Related

Conditional Locking Of Cell Based On Another Cell

Oct 7, 2008

This is for Excel 2007. I simply want to lock cell H20 and make it's value equal to zero if anything other than 0 is entered into H19. And vice versa (make H19 equal to 0 and lock it if anything greater than 0 is entered into H20). So only one cell can hold a value not equal to zero at a time, and the one that is zero needs to be locked. Here's what I have so far. It locks H20 based on H19 being > 0, but doesn't do the vice versa part. And it also doesn't make H20 = 0 when it locks it.

View 4 Replies View Related

Conditional Locking Of Cells

Jul 29, 2009

Lock cells based on conditions? For example, in my spreadsheet I need my cell J42 to stay locked at all times unless my cell C12 reads "Klongtoey".

View 10 Replies View Related

Conditional Locking Of Cells- More Than One Sub?

Aug 19, 2009

I am trying to create a form that has multiple drop downs, which are dependent on answers from other drop-downs. I'm trying to make it super user friendly and have the cells lock, so that the user can tab through only the items that they need.

I have it almost down, but I'm not sure how to do it for multiple cells. The closest I come is one big long if,else statement which runs through the whole thing on every cell change.

I have an example of the spreadsheet attached.
If F1 says "Other", I want H1 to be unlocked
If F2 says "Y", I want F3 and F4 to be unlocked
IF F4 says "Other", I want H4 to be unlocked

View 2 Replies View Related

Conditional Locking Of Button

Jun 17, 2008

I have a sheet that people fill in using a combination of validation lists and manual imputs, the requestor then clicks a button which automatically emails the order form to their line manager for authorisation. they add their initials and click another button to email it to accounts. All very nice, but people are lazy and we are starting to get incomplete forms through.

View 9 Replies View Related

Conditional Locking Of Cells ..

Sep 1, 2007

I have a condition whereby if cell C7 has a value entered, then cells D7 & E7 cannot have values entered. Like wise, if cells D7 & E7 have values, them cell C7 cannot. Cell F7 would be the product of the calculations using the data either in only C7 or only in D7 & E7. Please see the sample attached worksheet with my problem elaborated.

View 9 Replies View Related

Locking Conditional Formatting In 2000: Copy/paste Text From Other Cells Or Columns Even Other Workbooks

Jul 27, 2009

I have a column "g" with this conditional formatting:- =A2<>A3 Format Bottom Border.
However I will pass this workbook onto someone else who will fill in the text in column "g". They will use copy/paste text from other cells or columns even other workbooks that will not have the conditional formatting.

I have used Cells > Projection > Locked unchecked then used Tools > Protection > Protect Sheet and checked all. There does not seem to be a way to unlock the cell but protect Conditional formatting. Each time I copy and paste from other non formatted cells it wipes out my formatting.

View 4 Replies View Related

Locking Cell If Value

Mar 29, 2007

I am trying to unlock a cell if another cell is a certain Value and if it is not that Value to lock again i have got as far as unlocking it but i can not get it to lock if the value is faulse. This is what i have so far!!

Private Sub Worksheet_Change1()
If ("K4") = "Daily" Then
ActiveSheet.Unprotect ("Password")
Range("F6").Locked = False
ActiveSheet.Protect ("Password")
End If
If ("K4") = "Monthly" Then
ActiveSheet.Unprotect ("Password")
Selection.Locked = True
ActiveSheet.Protect ("Password")
End If
End Sub

View 2 Replies View Related

Locking Hyperlink To A Cell?

Mar 30, 2012

I am working in a workbook that uses hyper-links to navigate between locations on various sheets.

When rows are added to a sheet above the destination cell, the hyper-link no longer points to the original data but has moves the corresponding number of rows that have been added.

Is there anyway the hyper-link can be locked to a cell and adjust automatically if new rows are added or existing rows deleted?

View 3 Replies View Related

Locking Multiple Cells On Value Of Another Cell?

Jul 16, 2013

I have an excel sheet which will be password protected and only opened as Read-Only. There are 3 cells which have a dropdown list associated to select YES or leave blank. When YES is selcted in any of the 3 cells i want to lock the other 2 cells.

View 9 Replies View Related

Locking Specific Cell Blocks

May 7, 2014

When cross linking cells from one file to another, I sometimes lose the source link to rearranging cells on one of the other spreadsheets. Is there a way I can "lock" the source cell so that it isn't lost as a reference?

View 1 Replies View Related

Locking Cell After Data Entered?

Jan 27, 2013

I have a command button [Start] that when clicked, will enter the time the process started. I then wish to have the cell locked so the time cannot be changed after it has been entered. I tried this code:

Private Sub CommandButton7_Click()
Range("I5").Select
ActiveCell.FormulaR1C1 = Now()
Cell.Locked = True
End Sub

But am getting an error for the "cell.locked = true"

Any proper code to lock the cell after the data has been entered?

View 4 Replies View Related

Protecting Or Locking Cell In Certain Column

Feb 26, 2007

In Excel 2007 I protect cells in certain cloumns in a large amount worksheets. By manually protecting or locking them. Once the sheets are protected without a password just blank and only select unlocked cells is ticked for sheet protection. I close the workbook and reopen it. These cells are now unprotected that were protected and theres always two ticks checked off for select locked cells and select unlocked cells.

View 9 Replies View Related

Locking A Cell After Data Entry

Nov 17, 2008

I want to lock cell after cell in a worksheet after data entry. Entering the data is what drives the locking of the cells. All cells with data are locked but those without are unlocked.

View 9 Replies View Related

Locking Cell References For A Whole Range At Once

Dec 8, 2008

how to lock ALL cell references in the formulas within a range in one go, rather than ahving to go to each and every cell and lock them manually?

I have a table with 966 cells all with just one cell reference like this: .....

View 9 Replies View Related

Remove A Hyperlink Without The Cell Locking

May 24, 2007

I want to remove hyperlinks from a spreadsheet, but I want the cells to STAY unlock and I want the borders to not go away as well.

When you have a hyperlink and you delete the hyperlink it automatically deletes the border and sets the cell to locked - even if the sheets is password protected.

I want that if a sheet is opened all the links are just text! (I know the ' trick, that doesn't help) I was doing this in VB, put the "manual" method does the same thing.

View 9 Replies View Related

Locking Cell Dependent On Data Entered

Oct 18, 2009

I'm working in MS Office Excel 2003. I have a spreadsheet with numerous drop down boxes and references to named ranges. Here's my current quandary:

In cell I12, I have a drop down box with listed choices A, B, C and D. In cells I15, I18, I21 and I24, I also have drop downs.

What I'm trying to do is when choice D is picked from I12 it locks cells I15, I18, I21 and I24 so that the choices in the drop down cannot be picked. Also, a popup message is displayed.

I was able to receive help in getting this pretty much taken care of. But the problem I'm having with the code is that the popup message is also being displayed whenever I make a choice from other drop down boxes. So if I choose option D from I12, it locks the other 4 cells and displays the message just fine, but then if I choose anything else from say K12 or L21 then the message continues to come up. Here's what I have...

View 5 Replies View Related

Locking Cells Depending On What Is Entered In Another Cell

Sep 22, 2009

I'd like to lock some cells depending on what is put in a particular cell. As shown by the file attached, if the first column (Amendments) has a "no" in A4, I'd like to lock out cells B4:D4. (The sheet will be protected). If it's a "yes" then the cells are unlocked. I'd like to then apply this to all subsequent rows (i.e. lock B5:D5 if a "no" in A5 etc.)

View 5 Replies View Related

Locking Multiple Cell Reference Formulas At Once

Jan 15, 2010

I am working on a massive Pivot Table but in order to compile it I have to copy data from many worksheets into one. My problem is that each worksheet contains about 11,500 formulas with references to other workbooks and cells. However, none of these is locked for cell reference (meaning none has the $ sign for row or column) so copying and pasting obviously screws it up since the reference is changing but I need to have the links active in the pivot table.

It would obviously take me a year to add 440,000 individual $ signs. Is there any way, a trick of sorts, where I can just highlight the entire worksheet and add the $ sign to each and every cell reference automatically?

View 7 Replies View Related

Locking Cell Dependent On Data Entered..

Oct 10, 2009

In cell D10, there is a drop down menu with choices A, B, C and D. Cells D13, 16, 19 and 22 also have a drop down menu. What I would like the code to do, is when option D is chosen from D10's menu, cells D13, 16, 19 and 22 are locked so that the user cannot choose an option from any of those cells.

So basically, if D10="OptionD" then [whatever the value is to lock cells D13, 16, 19 and 22 from drop down use)

If possible, I'd like this as an IF function, and not a VBA macro. I'm not completely familiar with all of the IF functions...um...functionalities, so forgive me if I ask the impossible. But if the IF cannot do it, VBA code is fine.

View 9 Replies View Related

Toggle/Switch Cell Locking/Protection On/Off

Sep 4, 2006

I work in the field of education and the spreadsheet I'm working will require pupils to enter answers into the spreasheet. When a command button (New Questions) is pressed I want the VBA code to switch the cell protection (locked) on or off depending on a random value in cell c2. If c2 = 1 I want f3 to be locked, if c2 = 2 then d3 needs to be locked, but not f3. I've attached a copy of the spreadsheet to this email

View 5 Replies View Related

Locking And Unlocking Range Based On Another Cell

Jan 29, 2007

I have copied from another post (here I believe) and modified for my situtation. I simply want a range of cells K13:U13 to stay locked unless there is an X in H13. This will repeat on down the spreadsheet, ie. K14:U14 will stay locked unless there is a X in H14. Using the script I found it seems to be working unless you go back and change one of the X's to a blank. The range stays unlocked.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strName As String
Dim strRange As String
strName = Me.Range("H13")
If strName = "" Then
Me.Cells.Locked = True
Me.Range("H13").Locked = False
End If
strName = Me.Range("H14")
If strName = "" Then
Me.Cells.Locked = True
Me.Range("H14").Locked = False
End If..................................

View 6 Replies View Related

VBA Code For Locking Cell Disabling Dropdown Validation Lists

Mar 28, 2012

I am currently working on spreadsheet which is using a mix of Conditional Formatting, Validation and a bit of VBA code.

The spreadsheet is a log of vacancies and applicants, When "vacancy" is selected in A I have introduced some VBA code to lock down cells S:AT on the same row.

The code is working but the issue is it is stopping you using the drop down validation throughout the sheet, if you click the arrow that appears on the cell nothing happens, However you can still access the dropdown by right clicking and choosing the 'Pick from Drop-Down List' Menu Item.

The validation is set up by ranges from another Worksheet.

The Code I am using is:

Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
Dim myrow
Dim StarLock, EnLock

[Code].....

View 2 Replies View Related

Excel 2010 :: Enable Cell Locking Based On Value In Another Cell?

Jul 17, 2014

Is there a way in Excel (2010) to lock data from being entered into cells based on the value in another cell? Here is an example:

Row 4 contains dates from 2015-2030 as a header starting at column C (C4 and on) that represents years of revenue. Column B contains dates as well, this date can be any year but this becomes the driver for the data input in columns C through X. Column B contains the "Delivery Date"

Data is input in Columns C and so forth. The issue becomes that let's say that the date in C4 is 2016 and this is the beginning year. Obviously there should be no inputs for 2015 (C5) and the data should start to be input for this year at 2016 (C6). Often people just begin filling in the first available year not looking at what the year actually is.

Is there a way to add a formula or some logic/protection to prevent inputs in previous rows based on the values in column B? So if the value in column B is XXXX than there can be no inputs in cells less than that value?

View 6 Replies View Related

Shading And Locking Multiple Cells From Input Based On Value In Separate Single Cell

Sep 23, 2013

Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.

View 5 Replies View Related

Code Locking Up

Apr 13, 2008

I'm trying to run the below code, but every time I do, it locks up excel. The code is running on 900 rows of data within the "loop to delete rows" section and in the main for loop there are 49 values to evaluate for the string strSA. how I can improve this code? When I step through it with F5 it works fine.

View 14 Replies View Related

Locking Column A Only

Dec 10, 2008

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 1) = Now
End If
Application.EnableEvents = True
End Sub

everytime i lock the column a by using the protect sheet, the vba does not work or debugs. is there a way for me to lock the cell in column a when data is inputted or changed in column b? i am using date and time for column a

View 4 Replies View Related







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