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