Forum Discussion
Allow data Input but don't allow Edit
Seeking for some help again with these formulas. Have been working on project where multiple people are on the same excel sheet with scanners inputting data. The issues I'm running into is when people aren't paying attention and scan over existing data the information gets overwritten, we lose all that info. I currently have their scans matching another list of info (duplicate) it will go a certain color depending on its category. With that being said I was wondering if there is something I can setup to not allow any more changes if the info inputted was a match with another cell.
This is an example of the info I use:
So, if another person comes and scans radio 456 into the cell that has 789 instead of picking another blank. Now I will only 1 radio fully charge (456) instead of having both 789/456.
Please let me know if this is possible.
Try to use Data Validation in combination with Worksheet Protection.
Insert a New VBA Code:
In the VBA Editor, double-click on the sheet where you want this behavior (e.g., "Sheet1")
Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the cell is within the allowed range for input
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
' Disable editing after the first input
If Target.Value <> "" Then
Target.Locked = True
End If
End If
' Protect the sheet to prevent further editing
Me.Protect Password:="yourpassword"
End SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
' Unprotect the sheet to allow initial editing
Me.Unprotect Password:="yourpassword"
End Sub
---------------------------------------------------------------------
Remember to replace "A1:A100" with the range of cells where data entry is allowed, and also replace "yourpassword" with a password of your choice.
+++++++++++++++++++++++++++++++++++++
2nd Method:
Select all the cells in the sheet.
Right-click and choose Format Cells.
Go to the Protection tab and uncheck the Locked option.
Select all the cells in the sheet.
Right-click and choose Format Cells.
Go to the Protection tab and uncheck the Locked option.
(This will make all cells initially editable)
Select the range of cells where you want to allow data input (e.g., A1).
Right-click and choose Format Cells.
Go to the Protection tab and check the Locked option for these cells.
Go to the Review tab and click Protect Sheet.
Set a password to protect the sheet.
Please ensure Select unlocked cells is the only option checked.
+++++++++++++++++++++++++++++++++
3rd Method:
If you want to keep it simple and just give users a warning when they attempt to overwrite existing data:
Select the range where users are inputting data (e.g., A1)
Go to the Data tab > Data Validation > Data Validation
Set the Allow option to Custom
Enter this formula: =ISBLANK(A1) (replace A1 with the first cell in your range)
Set an Input Message and Error Alert to notify users that overwriting data is not allowed
Use conditional formatting to visually indicate which cells are filled.
Then select the range (e.g., A1)
Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Use this formula: =NOT(ISBLANK(A1))
Set a fill color to indicate that the cell is already filled
++++++++++++++++++++++++++
NOTE: in the above VBA Method is the most effective, as it will completely prevent further editing after the initial input, worksheet Protection can also be used to lock cells after initial input and data Validation can be used for a softer approach to give users a warning instead of completely blocking editing.Method number 3 seems like the best option for me. VBA unfortunately is not an option since the people using the excel are on an iPad and macros is not supported on the iPad (at least that I'm aware of). I'm having trouble getting data validation to work though because I can get it to prompt me that I can't edit the cell, but I get the same message even for cell that are empty. Not sure if I'm doing anything wrong but I copy pasted the exact formula you gave me.
Instead of using a static reference like =ISBLANK(A1), we need to ensure that the formula applies dynamically to all cells in the range.
Select the entire range where users will input data (e.g., A1:A100).
Go to Data > Data Validation > Data Validation.
Set the Allow option to Custom.
Use the formula: =ISBLANK(A1). The key is to select the correct starting point (e.g., A1), and Excel will apply this rule to each cell in the selected range.
(NOTE:- Adjust the Cell's according to your sheet and requirement.)
Please also make sure you don't have extra data validation rules applied on blank cells that are not intended for input. You can clear any previous data validation applied incorrectly by selecting the entire worksheet and choosing Clear All from the Data Validation menu, then reapplying to the desired range.
For Clarity suggest to use Conditional Formatting:
Select the input range (e.g., A1:A100).
Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Enter the formula: =NOT(ISBLANK(A1)).
Set a fill color to make it visually clear which cells are already filled.
Things to Remember:
When applying data validation and conditional formatting, ensure you're selecting the entire range.
The reference (A1) will adjust dynamically for each cell
This method only gives a warning. Users can still ignore the warning and overwrite the data. If stronger protection is needed, consider protecting the worksheet with locked cells
