Locking A Cell After Data Entry
Nov 17, 2008I 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
ADVERTISEMENT
Locking Cells Upon Entry Of Specific Data
May 14, 2009I'm creating a spreadsheet for work, which for security reasons requires certain information to be locked except to administrators. However the cells need to be open for inputing information for everyone until the administrator enters specific data.
Completed Date Time W/R No. Acc. No. Priority? Vunerable? Comments Closed Date/Time Yes 14/04/09 11:30:00 1234567890 8890976 Yes Yes N/A 14/04/2009 11:30
What I am looking to happen is once an adminstrator selects 'Yes' from the drop down menu on the Completed column, for that row of data to be locked for editing, unless unlocked later by an adminstrator.
Locking Different Cell Ranges Then Locking Whole Sheet With Macros
Jan 4, 2010I 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]
Locking One Cell If Another Had A Value In It (conditional Locking)
Oct 10, 2008I 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 RelatedLocking Cell After Data Entered?
Jan 27, 2013I 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?
Locking Cell Dependent On Data Entered
Oct 18, 2009I'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...
Locking Cell Dependent On Data Entered..
Oct 10, 2009In 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.
Getting Entry In One Cell To Cause Entry In Another Based On Entry In 3rd
Mar 20, 2014I 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.
Using A Single Cell For Data Entry
Jun 24, 2009Basicaly what I am trying to accomplish is the use of a single cell to enter the values 1 thru 60, so starting at e2 for instance, if I enter the number 1 then cell f2 will populate with a 1, if I were to enter the number 18 then the cell w2 would populate with an 18. So the range of cells that need to populate in this example would be (f2:BM2) for row 2. Furthermore if I were to enter a -1 or a -18 in cell e2 then those numbers would be eliminated from the appropriate cells on row 2. The next 299 rows would need to perform the same way for a total of 300 user input rows.
The way that I'm imagining e2 would behave in the example would be...type a 1 then enter....18 then enter....31 then enter and so on. Columns f thru bm would locked and unselectable....so a tab should send me back to a3.
Reasoning.... this is a simple scoring system based on 60 codes and data integrity is very important and I want to eliminate as much human error as possible. I will be setting autofilters and such later on.
I am attaching a screencapture as well.
Referencing Last Cell With Data Entry
Mar 31, 2007I have a table where the first column contains the date and the second column contains number of calls answered on that day. I have all the dates from Jan 1, 2007 - Dec 31, 2007 listed in the first column. I would like to be able to show the last date where data was entered at the top on my page where I have my Year-to-date running total. I would like the date to be automatically updated to the last datewhere data is entered.
View 2 Replies View RelatedForce Data Entry If Another Cell Contains Data
Aug 21, 2007I've been searching but I can't find an answer that fits. Here's the closest I've come. Force Date Entry on Particular Condition. What I need is if A10 has any data in it then I want a box to pop up that instructs the user to input data into B10 they click OK and then enter the data. Once the user inputs data into B10 then the error should be cleared. If they don't enter data then the error should keep coming back.
View 3 Replies View RelatedHow To Auto Lock Cell After Data Entry
Jul 31, 2012I 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 RelatedPrevent Hyperlink On Cell Data Entry
Jun 22, 2009Using 2003 - Is it possible to prevent cell data entry automatically creating a hyperlink for email/web url's. Using
View 4 Replies View RelatedCreating An Entry By Extracting Data From A Cell
Jan 10, 2007I have been sent a spreadsheet that i need to extract the first 4 characters of the data in column C and put that data into column D.
How Do I Make One Master Cell For Data Entry
Feb 21, 2009How do I setup one "master cell" to be used as the permanent entry point for dollar values of all entries performed as needed....and have that value be automatically added to the cell of the month reflecting the day the data was entered?
I am looking to keep track of purchases on a monthly basis. (e.g. I have 35 receipts for today...I just want to grab one at a time, enter the total for each receipt in one cell and have it add the value to which ever month's value based on the day I enter it.
Force Data Entry In Cell In Order
Dec 30, 2007I found the following code which works fine until I start to select multiple cells in cell B for deletion etc. At this point it throws up a run-time error 13 type mismatch, which will cause problems when other people start to use the spreadsheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target.Offset(0, -1) = "" Then
Target.Select
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
MsgBox ("You must enter Atlas Part No. first")
End If
End Sub
Multiple Data Validations With List And Cell Entry
Jul 2, 2013I have a scenario where a cell has to be restricted based on the user input of another cell, also it should allow only Yes or No values.
ex:column A
column B
pending response
submitted
User should be allowed to enter a value in column B as "yes" or "No" only when column A has a value "Submitted"
I referred the thread to restrict entering the data [URL]...
However not able to add list as well.
Cell Selection And Data Entry In Binary Matrix
Jul 4, 2013I've set up a binary matrix, such that I have 421 unique headings repeated twice: once in each column and once in each row (i.e., in the matrix the headings for row 5 and column 5 will trace back to the same description in the column headings and in the row headings).
I need to fill in 1s (case satisfied) or 0s (not satisfied) in this 421X421 matrix. Where my problem lies is in finding the exact row-column cell combination in which I need to enter the 1s. Descriptions that belong to the same group or category will be marked by these 1s in the matrix. For example, if I have descriptions 5, 9, 13, and 17 belonging to the same group I need to enter a 1 in each possible combination of these numbers: {5,9} {5,13} {5,17} {9,13} {9,17} {13,17}. I need a quick formula that will compute all possible combinations for inputted descriptions AND will enter a 1 into the cells, which correspond to these combinations.
Prohibit Data Entry Based On Adjacent Cell
Mar 14, 2013I have the following formula in cell H2:
=AND(EXACT(G2,UPPER(G2)),(LEFT(G2)>="A")*(LEFT(G2)<="Z")*(MID(G2,2,1)>="0")*(MID(G2,2,1)<="9")
*(MID(G2,3,1)>="A")*(MID(G2,3,1)<="Z")*(MID(G2,4,1)>="0")*(MID(G2,4,1)<="9")*(MID(G2,5,1)>="A")
*(MID(G2,5,1)<="Z")*(MID(G2,6,1)>="0")*(MID(G2,6,1)<="9")*(LEN(G2)=6))
I would like to prohibit data entry in G2 if H2 reads "false" and allow data entry in G2 if H2 reads "true" .
Copy Data Based On Cell Entry To Different Spreadsheet
Nov 10, 2008i am trying to find a way to automatically copy information from a worksheet on my computer([list.xls]-List of accounts) to a worksheet on a shared folder([summary.xls]- accounts that have paid)
example:[list.xls]Sheet1!A:A has account numbers, C:C has account balance and D:D has notes on account.
If D:D is "paid", copy acc# to [\foldersummary.xls]Summary!A1, balance to B1 and notes to D1.
If D:D is "payment pending" do same as above but in row 2 and so on.
Formula For Showing Data Depending On Cell Entry
May 18, 2007I need a formula that displays the word "Day" or "Days" depending on cell entry i know the formlula =IF((B12)>0,"Day","") to show the word day but i want it to show "day" if a 1 is entered or "days" if any number over 1 is entered.
View 2 Replies View RelatedUsing Data Validation To Limit Entry Of Number Of Characters In A Cell
Jan 24, 2014When you want to use Data Validation to limit the entry of the number of characters to a cell does this apply only to the cell that you are entering the characters or can it also apply to a cell that contains a LEN(A1) formula, for instance? Also,does the Data Validation limitation function includes spaces as well? Will the message appear while you are entering the characters (when it has reached the limit) or will it wait until you have hit Enter?
View 1 Replies View RelatedSingle Cell Entry To Multi Cell Entry
Jul 7, 2009i 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
Locking Cell If Value
Mar 29, 2007I 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
Efficient Entry Of Data & Automatically Have Their Price Show In Adjacent Cell
Aug 13, 2009I own a Hair Salon which collates its daily takings using a spreadsheet. A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end -of-day "till-up" process to reconcile purchases and services rendered aginst cash and cheques received.
Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price.
All of this is subject to human error. What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.
Data Connection Locking Source File
Jan 10, 2012I have 2 excel files. One excel file (File A) is a basically a source file on a shared network that users input data into. The second excel file (File B) I created in hopes of basically using to analyze the data in File A. I used a data connection to access the data on File A while on File B. The issue I'm facing now is that when I refresh the connection, it locks File A from being opened until I close File B. Currently, I have the data connection to refresh only manually, so I'll open File B, refresh it, save, close, and then re-open without refreshing, so that way File A only stays locked for that short amount of time. Is there any way to make it such that when I refresh the connection, it doesn't lock File A?
View 1 Replies View RelatedExcel 2010 :: Enable Cell Locking Based On Value In Another Cell?
Jul 17, 2014Is 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?
Conditional Cell Locking?
Mar 8, 2013Is 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.
Locking Hyperlink To A Cell?
Mar 30, 2012I 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?
Wrong Entry Into Data Validated Cell Triggers Debug On Change Event
Dec 14, 2011I have a Change Event macro that works properly. I also have several cells that contain a drop down list. If a user tries to enter an incorrect entry...the debug is triggered on my even change macro.
Here's the piece of the Change macro that has issues. Specifically, the .undo line highlights.
With Application
.ScreenUpdating = False
.EnableEvents = False
Set SelectedCell = ActiveCell
myTitle = Cells(Range("Titles").Row, Target.Column).Value
myRow = Target.Row
.Undo: myBefore = Target.Value
.Undo: myAfter = Target.Value
SelectedCell.Select