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
ADVERTISEMENT
Oct 22, 2008
The process that will be used is that, I would set up the sheet every month and send it to a data entry person. Then they would send it back to me, and i would send it to certain people for approval. Once they respond with approval I then send it back to the data entry person for filing.
I'm hoping to create a button that will "flip" a switch to to speak.
When I send it to the data entry person the first time, I need certain cells locked and unlocked.
When I send it to the people for approval I need the whole thing locked as well as for when I send it back to the data entry person for filing.
I know how to do this manually, but my issue is that every time I need to send it back to the data entry person at the beginning of the month I have to go over every data entry cell and set it up so that when I lock the sheet they stay unlocked.
So that's where the button would come in...it could even be 2 buttons...Data Entry Locking and then a full Sheet Locking...I would need each password protected too.
View 6 Replies
View Related
May 25, 2014
My problem seems quite easy to solve but for some reason I cannot get around the error messages. It seems it will not process the paste values argument:
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
VB:
Dim currentWb As Workbook
Dim MasterWb As Workbook
Dim wbName As String
Dim ToolWsName As String
Dim MasterWsName As String
Dim k As Integer
[Code] .....
View 3 Replies
View Related
Jul 5, 2012
I need to unlock and lock certain cells when other cells have data entered.
I also need to enforce in the unlocked cells a minimum value.
In my worksheet i have the below requirement
Initially Cells C6 and 7 are unlocked and Cells C5, 8 and 9 are locked
1. Cell C6 must have a value entered greater than or equal to 50, when this value is entered I need to unlock cells C5 and C9 and lock cell C7
2. Cell C7 must have a value entered greater than or equal to 50, when this value is entered I need to unlock cells C5 and C8 and lock cell C6
I also need to unlock cells C15 and 16 when C13 has "Yes" selected in the drop down menu.
View 3 Replies
View Related
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
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
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
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 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
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
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
Feb 22, 2008
i have an excel sheet that has a cell with a formula in it. This cell has been locked by the previous user.
I am unable to click on it and it because it is showing a #NAME? error, i need to edit it somehow. Is there some way i can edit it?
View 9 Replies
View Related
Jun 7, 2014
I have this code:
Code:
Private Sub WorkSheet_Activate()
'Open workbook and default all main page dynamic data
Application.EnableEvents = False
With Worksheets("MAIN")
.Unprotect
.Range("F3").Value = ""
.Range("O5,T5, F6") = ""
[code]....
It is triggered when protected worksheet "MAIN" is activated. EnableEvents=False prevents any worksheet change triggers as cells are defaulted. Ranges F3. O5, T5, and F6 are cleared. Rows 5 and 6 are hidden (to be revealed again in later code).
Cell F3 is a cell in which user input is required via a validation list. Since the worksheet is protected, it is necessary to unlock range F3 to allow the user to enter data in the cell. EnableEvents is set backto true to allow the worksheet change to trigger when the user enters data in F3.
I am receiving an 'Application-defined or object-defined error' with the line highlighted in red above. (the line that is supposed to unlock cell F3)
View 3 Replies
View Related
Dec 17, 2009
I have a macro in my worksheet that runs as a change event on certain comment cells. This macro unprotects the worksheet, expands the merged comment cell that just change, and re-protects the worksheet. The cell expansion works great. When I re-protect the worksheet all of the cells work correctly, except the cell that was just changed. Any other "unlocked" cell in the document stays unlocked after the protection is applied, but the cell that just changed becomes locked. I need this cell to remain unlocked to allow the user to update the comments. Here is my full
View 3 Replies
View Related
Jun 28, 2006
I have a query in relation to locking cells to disallow editing based on a dynamic range. The VBA code I have thus far is this :
Sub PasteForecastWO()
rowarr = Array(10, 26, 28, 69, 72, 79, 81, 87, 89, 104, 106, 114, 116, 122)
For Each ce In Range("S8:AD8")
If ce = "ACTUAL" Then
For i = LBound(rowarr) To UBound(rowarr) Step 2
Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value = Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value
Next i
End If
Next ce
End Sub
What I would like to do is lock the range that applies as the variable "ACTUAL" moves or changes along the range S8:AD8. I have attached the relevant sheet so that you may get a clearer picture.
View 7 Replies
View Related
Jan 13, 2014
I am having trouble with my VBA code, it hides groups of rows (projects) based on the contents of corresponding cells above. these have either 'yes' or 'no' in them (indicating if the person is active in that project). The Yes/No is formula based from another sheet.
what I am trying to do, is have particular cells, withing the projects rows, locked (as they are also populated by formulas) but have other cells open for editing.
when I try and do this i get an error with my VBA, i am assuming that as cells are locked, the VBA is prohibited form hiding inactive projects/rows. i tried an addition at the bottom but this hasn't worked
Code:
Sub Worksheet_Calculate()
Dim i As Long, StartRow As Long, EndRow As Long
StartRow = 13
[Code]....
View 1 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
Oct 25, 2007
I have had a look around and found some answers to this question but not quite as complex and I don't know enough to adapt them correctly.
Basically AX35:AX239 contain a formula which returns "TRUE" or "FALSE" dependant on certain values in the row, what I need is for the corresponding H, I & J cells to be unlocked on each row if the outcome is "TRUE" and no action if "FALSE".
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
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
Feb 13, 2014
Excel can automatically recognise individuals by their windows login name so you can fully automate the protect/unprotect of the correct sheets without even individuals having to use passwords.
The first step though would be to gather the id's of all users.
1. Create an additional sheet and call it UserLog
2. Put the following code into all the worksheet code modules
[Code] ......
Once you know the windows login names and which sheet they are responsible for you can add the following code to each worksheet.
[Code] .....
I have tried as per below and it is not working.
I have about 50 user that uses one document and will record their comments to instructions, but only 20 users will be able to add the instructions. The document needs to autolock when it is saved and can only be unlock with the username of the 20 users.
View 1 Replies
View Related
May 7, 2008
I'm using three different workbooks for the macro I've designed. The macro works exactly as I planned it would, but I'm getting a curious side-effect of running the macro. I have over 5,000 workbooks that I need to open, unprotect, change (mostly by copy/paste), and reprotect. It's a fairly straight forward macro, but it is my first, and I must be overlooking something. When the macro is complete, cells that were not modified via the macro in any planned/anticipated way are being unlocked. This has left me puzzled and frustrated. The files are opened, unprotected, the cells are copied over as planned, reprotected, and closed/saved. Everything works perfectly, but the cells in parts of the workbook (Filename) (which were locked before the macro ran) have been unlocked and are no longer protected when the workbook is reprotected. Again, these cells were not within the ranges modified, and I'd rather not have to format every cell in every one of the 5 sheets of (Filename) to be locked.
File names are:
"Finished Goods Inventory TREE (ToDMSI).xls" (alias: Workfile - sheet providing file path to be updated)
"Random_workbook_selected_from_previous_filename.xls" (alias: FileName - the copy-to file being updated)
"MACRO TEST BOM Master.xls" (the copy-from file always open)
Comma Delimited table layout in "Finished Goods Inventory TREE (ToDMSI).xls":
Col A, Col B, Col C, Col D
Customer ID,Item Code,File Name, Directory
4FRE01,4FRE01-0001,4FRE01-0001.xls,4FRONT
Here's the
Public FileName
Public Workfile
Public ItemCode
Public CustCode
Sub OpenBOMSeq()
View 14 Replies
View Related
Dec 3, 2008
Testing my worksheet, I found that if I double click on any cell that is not locked, the worksheet becomes unprotected. I need to prevent this from happening. I tried the following code, but it didn't work.
View 6 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
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
