Macro To Lock Cells After Data Entered
Mar 22, 2007I need a macro which will lock the cells after data is entered in the cell once. Cell should become non editable and should be only edited after a password is provided.
View 4 Replies
ADVERTISEMENT
Automatically Lock Cells When Data Entered
Apr 16, 2008I am trying to apply a conditional cell lock to a worksheet. I am using Excel 2003, not 2007.
In column J, I have a list validation being used. The default value is "Not Claimed", I want to lock the cell from being edited once the value has been changed to any other value on the list.
The worksheet will be password protected to prevent entries into static fields (A:I & N:P).
I also would like to give the user the ability to unlock a specific cell, not the entire worksheet, by entering a password or something if they accidently choose a wrong value from the list.
I have column Q and beyond available if I need to have the password entered somewhere else.
I have attached a sample of the file I am working with.
Excel 2007 :: Auto Lock Cells Once Data Is Entered Not Working
Aug 14, 2014I 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
Excel 2010 :: Lock Out Cells If A Value Is Entered In Another Cell
Mar 14, 2012I am trying to lock out cells if a value is entered in another cell in Excel 2010. For instance, when entering a value into N7 (merged Columns N7,O7,P7 into one - not sure if that matters) I want Q7 (same thing, merged Q7,R7,S7 into one) and T7 (merged T7,U7,V7 into one) to be locked out.
So, if N7 has anything typed into it, I would like Q7 and T7 (merged) to be locked.
If that's possible, I would also like for it to happen from rows N7 - N21 ...
Is this hard to do? My understanding is that it's only possible through VBA ... am I accessing the script correctly? (right click the 'sheet 1' tab [named Issues log], select 'view code').
Lock Cell After Data Entered And Have Moved Off That Cell
Jan 5, 2013Currently 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 RelatedAutomatically Lock / Protect Formula Cell When Entered
Feb 26, 2008I need to create a macro to where once a cell has had a formula or data inputed that it locks afterwards and cannot be edited without unportecting the sheet or not at all. The problem is I have no idea how to program in VBA. I can get there and select the worksheet but after that I am stumped. How would I enter the macro and what should the macro look like?
View 2 Replies View RelatedHow To Make Macro To Lock Certain Cells Connected To Another Cells Value Then Copy Daily
Mar 29, 2014am making Excel for private use, i need the following makro to be active, here is the idea because i didnt yet work with makros at all:
If A1 = False
then Range B1:D1 will be locked cells
If A1 = True
then Range B1:D1 will be unlocked cells
Those rules apply to 1 day in the year.
the range A1:D1 will be copied about 400 so the makro should be active to each day separatly.
Check the picture attached for example of one day
Dynamic Macro To Lock Cells That Contain Any Value
Feb 10, 2014I'm currently using this macro:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng
Dim MyCell
Set Rng = Range("A1:AA7672")
For Each MyCell In Rng
[Code] ....
The problem is that it is not dynamic and it's super slow. The larger my range is the slower it operates and right now it's almost unusable.
The workbook I am using gets touched by 10 different people and I want to prevent tampering. I want for the workbook to be, when protected, have all the blank cells editable and once a value is inserted, it cannot be edited again and not work so unbelievably slow.
Automate Macro And Lock Cells
Oct 6, 2008I have a macro in Module1 of workbook "BAU" called "ExtractToSheets" which exports data depending on the contents of a cell (Thanks royUK et al)
At the moment however, you must run the macro manually to export the data.
Now, i have put some simple validation in column K of worksheet "BAU Data" within the "BAU" workbook. Whenever a cell within a predefined range with column K is changed to "Yes" i want the macro to run AND i want the row the cell is in to be locked so it cannot be edited further by the user. I have had too many users mess up previous sheets i have made to collect my data, and i wish to make this one somewhat more idiot-proof!
Any pearls of wisdom folks?
I would have attached a representation of my sheet, unfortunately because there is now a macro in my spreadsheet, our trigger-happy security software won't let me post it!
Macro Filter And Then Lock Cells
Oct 18, 2012I have a search form that will filter the result based on the requested category. Is it possible to lock the cells once it has been filtered and then remove the lock after they close the workbook?
View 2 Replies View RelatedMacro To Lock All Non-Blank Cells After Saving Workbook
Jul 23, 2014I have been searching for a macro that would lock all the non blank cells (cells that have any value/text) after the workbook is saved.
When the user clicks on save a message box should prompt stating that "The data you entered has been locked successfully".
Macro To Lock A Number Cells After Changing A Cell
Jan 5, 2009I have an excel sheet which is protected (mainly so that the users will not change any formulas) the excel sheet is basically a list
Just to put you in the picture
Column B Is PO Number
Column C Is Remarks
Column D Date
Column E Description
Column F Supplier
Column G Amount
I would like that when Column C changes to "OK Checked" column B C D E F G are protected and locked. But obviously for that particular row.
For example the input of Cell C15 changes to "OK Checked" then B15, C15,E15,F15,G15.
the macro must NOT be fixed to a particular row (in this case 15)
Lock Cells Automatically After Entering Via Macro Code
Apr 23, 2008I have an excel sheet which has a macro which captures the current time in a cell with the formula =now(). What I am looking for is that once a user runs a macro and the time is inserted into that cell then that cell should be locked and cannot be changed then. If this can be done by any formula / function or VBA.
View 2 Replies View RelatedPost To Lock Cells With Data
Jul 12, 2007I believe it was yesterday this board had a post how to lock and protect cells with data and leave them unlocked and unprotected if they're blank, but I can't find it. Does anyone know where it was?
View 9 Replies View RelatedMacro To Lock Cells In Protected Sheet Depending On Criteria
Dec 13, 2007I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.
What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?
Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.
What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.
What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.
Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?
PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.
Conditional Macro - Insert Text And Lock All Duplicate Cells Except For First One
Sep 16, 2012I am new to VBA but I am trying to insert text and lock all duplicate cells except for the first one. Below is the macro I am trying to make. I keep getting an error on the line:
If(COUNTIF($A$9:$A$76,A9)=1,FALSE,NOT(COUNTIF($A$9:A9,A9)=1))
This is my macro:
Sub SetVoters()
'
' SetVoters Macro
'
'
Sub SetVoters()
'
' SetVoters Macro
'
'
ActiveSheet.Unprotect
[Code] ......
Lock Cells To Prevent Predefined Range Macro Copying
Oct 30, 2012I have a spreadsheet that allows users to paste set data from a PDF Image (using OCR) straight into Excel and then use the MID function to split the data accordingly.
Unfortunately, the OCR isn’t too intuitive and gets it wrong sometimes.
So to counter this, in another sheet (in the same workbook) I have a manual input section, and a simple macro button that pastes this data into the same fields where the OCR text would be, so that the main sheet works exactly the same way as before.
The problem is, and most likely due to the simplicity of the sheet, if a combination of OCR pasting and manual inputting is used, when I hit the paste button, it over rides the OCR data with blank cells
In the link below I have shown what is currently happening (1, 2, 3), and an example of what I would actually like it to do (4, 5, 6).
Example - Online Spreadsheets - EditGrid
So, in the 2nd scenario, I would like “5” to recognise that the respective cells in “4” already contain data and fill them ‘Grey’. This I have already achieved with basic conditional formatting.
However, I need to take it 1 step further and say that if the parent sheets cell (Auto OCR) contains data, as well as filling cells (in sheet Manual) lock these cells off and prevent the end user from adding data and/ or being copied over to the parent sheet.
Is this possible?
The result then being the parent sheet with both OCR text and copied text from the manual input sheet.
Both sheets are protected anyway and only allow for user input in certain areas, so is it even possible to apply further protection once the sheet is locked already?
Lock Certain Cells To Allow Data Entry But Not Allow Data To Be Removed
Apr 23, 2013is there a way to lock certain cells to allow data entry but not allow data to be removed.
View 2 Replies View RelatedVBA To Lock Cells After Data Entry And Save
Aug 13, 2013I 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 RelatedProtect / Lock Cells After Data Input?
Mar 15, 2013I have a spreadsheet whee teammates can inut the date in one cell and the exact time in another cell - how do I lock the cell with the time so it can't be changed?
View 1 Replies View RelatedLock Cells With Data Validation List
Oct 1, 2009
I need a macro which will lock the cells after data is entered in the cell once. Cell should should be only edited after a password is provided. I found the following macro but the problem is that it's not locking the cells where I have Data Validation List :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:M10000")) Is Nothing Then 'set your range here
ActiveSheet.Unprotect Password:="1234"
Target.Locked = True
ActiveSheet.Protect Password:="1234"
End If
End Sub
Macro - Sorting Data To Other Worksheets / Update As New Data Entered
Jan 30, 2014I am trying to create a "Master Sheet" where I enter in the column data and after I have entered my data for each row, I can select the button which toggles the macro to run. I have it built to build new sheets as new clients are obtained. My problem is after I have a sheet that has client's data I cannot get new data to add itself below the data that is already there. I want each client's sheet to keep adding rows as more data comes in. My current macro is :
[Code] .....
Attached File : Data Entry Macro.xlsx
Cells With Instructions Which Disappear When Data Entered?
Jan 29, 2009creating a template for people. In the template, I want to create cells which instruct the user on what need to be filled in that cell.
For eg. go to [URL] In the box, it says 'Enter word or phrase'. When you click to enter a word, this line vanishes. I want to create a similar thing in Excel.
In Excel, for eg, in a cell it should say 'Enter Name' and when the user enter his name, this instruction should vanish. Also, this instruction should be in GREY and when they user enters his name it should be BOLD BLUE.
Cells With Instructions Which Disapper When Data Entered
Jan 29, 2009I needed some help in creating a template for people. In the template, I want to create cells which instruct the user on what need to be filled in that cell.
For eg. go to www.m-w.com In the box, it says 'Enter word or phrase'. When you click to enter a word, this line vanishes. I want to create a similar thing in Excel.
In Excel, for eg, in a cell it should say 'Enter Name' and when the user enter his name, this instruction should vanish. Also, this instruction should be in GREY and when they user enters his name it should be BOLD BLUE.
How To Unhide Rows When Data Is Entered Into Cells
Dec 6, 2009I have been searching the internet and forums for days now looking for and trying out macros and scripts that will automatically unhide rows once data has been entered into a cell. The data entered is a link to a cell in another sheet. Everything I have tried is close to what I am looking for but it does not quite work, and because I really don't understand the programming details, I don't know what to change in the script to make it work for my spreadsheet.
create a macro/script to do the following:
Various rows in my summary sheet will be hidden as they will probably not be used, but in the off chance that data will be displayed in them, I need them to automatically unhide. The data that might be displayed in them is a link from monthly record sheets i.e. "=Jan!F4" or "=Feb!F32"
And because I am taking data from a 12 month period, I need the rows to unhide if any data is displayed between cell range of F5:AC1705. It is a large spreadsheet of data and it is all linked to 12 other sheets.
Unfortunately, I am unable to attach the file as it is too large.
Change Cells Data Is Entered Into Based On Date?
Jul 19, 2014I own 3 restaurants and I use Excel to track my sales statics. I have to enter certain pieces of data on a daily basis and it can become slightly cumbersome when dealing with the sort of washed out look of Excel spreadsheets. I wanted to write a Windows application that would handle the process for me but my knowledge of programming languages is too limited and I don't really have the time to learn. What I'd like is to have a single page on my workbooks with which I can enter the data in on a field type view, and have it go to the appropriate cell on the "Raw" data sheets in the same workbook. For example:
Let's assume this is the Raw data sheet. I currently enter the data that I need here, and Excel does the rest for me. This gets hard to look at everyday when dealing with hundreds of numbers.
Net Sales
Taxes
Prior Year Sales
Percent Increase/Decrease
[Code].....
Entering the data here would automatically fill in the data on the Raw sheet, and would know which cells to put the data into based on the date, which would of course be gotten from the =Today() formula.
Automatically Color Cells In Relation To Data Entered
Aug 30, 2013how to do basic formulas in Excel but I are hoping that there is a macro or a VBA or something that can be made which might work for what i need.
I have decided to start a small tour shop (I make the reservations for guests with a hotel in the area and take them on tours of our region) and are looking to make a booking sheet for each month. Nothing complicated just simple. see the attached .xlsx file.
I was wondering if there is a way that when the fields in column A & B are filled in if it could automatically color the corresponding dates in for the same row in the calendar area? like I have manually done on sheet "October 2013"
[URL]
Data Validations - Cells Remain Blank If Nothing Is Entered On Sheet 1?
Jul 22, 2014On Sheet 1 I have Data Validation Lists set up. On Sheet 2 I have the information populating from what is entered on Sheet 1, only problem is...when I don't have anything entered in the cell(s) on Sheet 1 then the cell(s) on Sheet 2 reflect 0. I would like the cell(s) to remain blank if nothing is entered on Sheet 1.
View 4 Replies View RelatedMacro Copy / Paste Last Data Entered To Last Empty Row Of Different Worksheet
Mar 15, 2013I am looking to create a macro to be assigned to a button that copies the last row of data entered and then pastes it to the last empty row on a different worksheet. This is a dummy spreadsheet to work with (I have more data, but the concept is one in the same). Sheet1 ("Branch1"), Sheet2 ("Branch2"), and Sheet3 ("All"), the names in brackets are names of the sheets, but for ease I'll refer to them as Sheet1, Sheet2, and Sheet3. I have columns beginning in B as follows: Date, Branch, Currency, Coin, and Total (the branch and Total are tied to formulas, however I just need to the text values and formats to come over to the other worksheet). have the portion regarding the copy of the last row in Sheet1, however it won't PasteSpecial.Selection in Sheet3 as it says the cells are not sized or formatted correctly.
VB:
Sub CopyB2()
lr2 = Sheets("Branch2").Range("B" & Rows.Count).End(xlUp).Row
lr3 = Sheets("All").Range("B" & Rows.Count).End(xlUp).Row + 1
Sheets("Branch2").Range("B" & lr2).EntireRow.Copy Sheets("All").Range("B" & lr3)
End Sub
Keeping Cells With Formula And Conditional Formatting Blank Until Data Entered?
Feb 22, 2013I have a spreadsheet filled with formulas that depend on a value being entered into A2, A3, A4, etc... So column A starting at A2 is where I will manually input a number and the formulas I have in columns B, C, and D will import information from another sheet based off what is put in column A. In column D the formula I have to import data
is =IFERROR(VLOOKUP(A2,Master!C:M,11,0),"").
This will import another number. Additionally in column D, I have conditional formatting that will return a red, yellow or green light based off the rule I have in place. Everything works fine, the only problem is that column D has a green light all the way down even without a value being placed in column A. I would like to find out a way to keep the cells in column D blank until a value is entered in column A. Also, if I go back and delete the value in column A, I would like the corresponding cell in column D to go back to blank as well.