I 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
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've got a spreadsheet set up for a simple barcode scanning system. The user scans the barcode of the product when complete, and it logs it to a sheet along with a date & time timestamp. I'm then planning to use a v-lookup (barcode to product name) & pivot-table to count instances of 'product name' so that I can monitor what is being produced easier, nothing too tricky.
The problem I'm having comes from the products of the same spec having the same barcode, so data validation is hard to apply, as I can't 'not allow dupes' - the concern I have is that if the user unknowingly (or knowingly!) scans the same ticket twice, I think I've got 2 of a product when I've only got one. The idea I had was to lock all cells for a set time period (say 1 minute) after an entry so that it couldn't be accidentally scanned twice.
I need the attached spreadsheet to lock cells after data is entered into cells E13-G74 and the page saved. It will be opened twice daily and saved. Also im not sure if it makes a difference but I would like all other cells outside this range locked except for C5, C6, C7 AND C8. I have seen several threads on the topic but am extremely new to using VBA and cannot make anything work.
View 6 Replies View RelatedI have a spreadsheet for recording and summarising particular transactions. Column B records the date and Col C details the transaction type (from a data valdation list). Using conditional formatting the cell (in the same row) to whch the transaction value is to be entered is highlighted.
Other cells in the row contain formulas and are locked and the sheet is password protected.
On saving or closing the workbook I want to lock all the cells (used or unused) in any row in which there is a date in column B. How can I do this?
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?
How 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 spreadsheet where there are two columns for the user to enter data in, but they can only select one column. I want to lock the second cell if data is entered in the first. I can't find a way from validation to do this. Is there another way via VBA (stupid question, I know... VBA does everything but scratch your butt)
View 4 Replies View Relatedis there a way to lock certain cells to allow data entry but not allow data to be removed.
View 2 Replies View RelatedI have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
i have an address that is all in one cell displayed like
101 hampton Court, Hampton heath, Hampton Town, Hamptonshire, HA01 1AS
but i need to have it split in to individual cells so
Cell A1 would be 101 hampton court
B2 Hampton Heath
C2 Hampton Town
D3 Hamptonshire
E5 HA01 1AS
each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle.
also i need it to work backwards ie
it finds the post code first,
then the county
then the town
as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
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 RelatedI 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.
Is 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 RelatedIs 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 Related