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.
I'm creating an inventory of cars for a car dealership that has about 27 or so columns and 500 rows for entries. Currently there are 50 rows used to keep track of inventory and with the additional rows for future entries. I want to set it up where the data entered into the cells gets locked automatically when saved and reopened for future use and can only be altered if a password is entered. Table is C3:AE503.
View 5 Replies View RelatedI am attaching the example where I need to lock H2 to H1000, note that sheet is locked "anixter12 and I have also given range to user for modification I simply want to lock cells in H once date is entered.
View 3 Replies View RelatedI have created a time sheet and I would like to know if there is a way to auto-lock cells after time is entered so that it cannot be changed. As of right now I have employees enter the time by using Shift+Ctrl+;, so the exact time is recorded, however employees can write in whatever time they want. so I guess what I want to do is 2 things:
1- can the cell be formatted to only accept Shift+Ctrl+;, so that the exact time is entered in the cell and prevent employees from entering times they were not at work?
and
2- can the cell auto-lock after Shift+Ctrl+; is entered so that they can't modify the time sheet the next day if they arrived earlier than the day before?
is there any way to automatically sort a column from A-Z without need to choose column, sort&filter, a->z? maybe a pivot table?
View 1 Replies View RelatedHow would I go about lock a cell after there is an entry. Once the entry has been made the user could not change the entry. For example if a user enter in cell b2 he could not change the entry, he could change a2, c2, d2. He would also be able to put an other entry in b3, once enter the cell is lock.
View 5 Replies View RelatedI have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010
[URL]
I am new to Excel programming, haven't done any before and not familiar with creating/editing macros. Using Excel 2007.
I would like to automatically lock all the cells in a row if the date in column A in that row is >= 7 days old. Data is entered once a week (some time Sunday - Saturday) and I would like historical data (rows) to be locked so that they can't be edited inadvertently.
If possible could I also highlight the current working week somehow?
I have attached the worksheet : testworksheet.xlsx
I have searched and found many examples of code that will lock cells once data has been entered. But for some reason, I cannot get it to work in my workbook. The weird thing is, I have successfully got them to work on blank workbooks. I unlock a range I want people to be able to edit, I protect the workbook, I enter the code, and it works. But I do the same thing on my workbook, nothing locks.
I've tried locking all the cells on a sheet, and only unlocking a small range, as in the examples (A1:A10), and I cant get it to work. I've tried not pre-protecting the sheet, I've tried 4 or 5 different examples of code. If it wasn't for the fact I've gotten it to work on blank workbooks, I'd think VBA was disabled or something. I can't post a copy of my workbook for you all to look at because its on a stand-alone computer at work (and the USB is disabled for info-sec).
Excel Version 2007
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.
I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...
Auto lock cells after data entered and SAVED.
Auto Lock Selected Range of Cells After Saving
I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.
This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.
How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies View Related=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?
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 RelatedI 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
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 RelatedI'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?
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.
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.
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 RelatedIs 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 Relatedhow 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 RelatedI 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
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
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)
i am having problem with doing team, i would like to lock cell contain formulas but allow users insert row.
View 2 Replies View RelatedI 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 Related0: 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.
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 RelatedI 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.
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