Automatically Lock Cells When Data Entered
I 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.
View Complete Thread with Replies
Related Forum Messages:
Automatically Lock / Protect Formula Cell When Entered
I 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?
Lock Cells Automatically After Entering Via Macro Code
I 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.
Automatically Open Comment Box When Data Is Entered.
I have a spreadsheet where we calculate lost time of machinery and how much money is lost. Every day someone enters the hours that the machines were down and then enters comment in that cell stating what happened or what the resolve was. So for example Machine A runs 24 hours a day, but 12-19-08 it had mechanical issues and was down for 2 hours, so the entrant will enter 22 in that cell, then have to right click back in that cell, go to insert comment and enter "need to change belts." Then if he finds out later that they were down another two hours he would have to change the hours, right click in the cell and edit comment. What we have been coming across is that after awhile he just enters the hours and not the comment. Besides firing a lazy operator I would like to make it more user friendly and ensure that the data entry person is entering the comment.
I would like after the hours are entered in the cell for the comment box to automatically open. I figured there has to be a formula to do it, but i am not at an advanced level. Can you help me?
Extend Formula Automatically As Data Entered
I found the following code here.
For data entered into column A, it copies the formulas from columns B:E in the row above to the current row.
It works great except fot the first row (A9) where it copies the header row (B8:E8).
How can I get it to not copy when data is entered into A9?
Private Sub Worksheet_Change(ByVal Target As Excel. Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(1))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i)
Application.EnableEvents = True
On Error Goto 0
Lock Cells With Data Validation List
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
Target.Locked = True
Cells With Instructions Which Disapper When Data Entered
I 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
I 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.
Automatically Tick Cell If Entered Value Matches
I have attached a spreadsheet which, when you enter a matching value in the vehicle column, the cell that matches the vehicle name in both instances (column and row) is ticked. Think I am wanting to use a worksheet_calculate function but cannot figure out how to write the appropriate lookup in VBA. I do not want a formula in the cell.
Lock Cells Based On Value Of Other Cells - Code Simplification Query
I'm using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.
It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).
Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?
Lock Empty Cells In Range Based On Other Cells Content
I am trying to lock the unused cells in 32, 2 column by 7 row named ranges, based on whether or not two cells, above each range are equal or less than each other. In other words while one of the cells is less than or equal to the second cell all cells in the range below should be unlocked, as soon as that condition is no longer true the blank cells need to be locked.
I am trying to use this in the Workbook_Sheetcalculate so that the macro will run automatically.
Match Cells In Another Column And Populate Data Automatically
I have a worksheet where I need to match the work items from Region A to Region B. If the work items match , then I need the formula to automatically populate the corresponding data in the next 3 columns as similar to Region A. I have tried searching the forum and but I could not work out the formula. I am not sure if the formula is Vlookup or INDEX/Match which both I am not well verse. I have attached a sample sheet.
Stopping Cells From Automatically Refreshing After External Data Refreshes
I have 2 Excel documents.The first document is named "data" and it extracts data from off the Internet every 24 hours. The second document, named "database", is for storing this data. Every 24 hours, I run a macro which imports the data from "data" into a new row in "database", along with the date the macro was run.
My problem is this: Whenever I refresh the external data in "data", my "database" document also refreshes itself instantaneously. My question is this: Is there a way to "cut the connection" between the two documents so that, when "data" is refreshed, the data in "database" DOES NOT also refresh itself automatically?
Lock Certain Cells By Vba
I am aware of setting protection to certain cells by the Excel Options. which is unlocking the by defalut locked cells and then setting the sheet protect in tools->protection. I want this to be done through VBA code.
i have a template which has text in some cells and some cells are blank, i need to lock the cells which has text and keep the blank cells unlocked for user input. As this is a template it will be copied to another sheet, so i need to retain the protection properties in all new sheets (copied from the template). Attaching the template for which the protection needs to be done.As of now the sheet1 has protection property set through the toolbar properties.
Lock Cells On Condition
W3:W300 is a protected range containing formulas returning numbers from one upwards, with two decimal places.
I need some vba to check this range and, if a number greater than 1 is found, to make the corresponding cells in Columns X and Y blank and lock them.
Lock Cells Upon Selection
I am assigned to a very tedious task which requires me to lock selectively, cells from Jan through to Dec. Instead clicking the cells and selecting the various options from the menu bar, I would like to do it the other way.
If I want to go locking a cell upon selecting it, what are the VB codings?
Lock Cells Over Four Worksheets In One Go
I am trying to get user to enter month and goto sheet named the month they enter. I would also like to tag on the end of each one a 1,2,3,4 as I have four sheets four each month. Jan1, Jan2, Jan3, Jan4 etc..
Dim InptPass As String
Dim Pass As String
Dim month As String
Pass = "mypassword"
InptPass = InputBox("Please Enter Password")
If InptPass <> Pass Then
MsgBox "Incorrect Password", vbCritical
month = InputBox("Enter Month to Lock")
ActiveSheet.Range("A1:BK22").Locked = True
MsgBox "Cells Locked", vbInformation
When A Date Entered On A Specific Cell, Automatically Enter A Text In Other Cell
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
Lock Cells Together And Rank Rows
Basically I have recently started my own personal music chart and I'm having problems with a Year To Date page.
Basically all I want to know how to do is how to lock some cells together and rank many rows of those cells with 1 column where the total is?
I have attached the spreadsheet as a zip files as I was having problems uploading it as a xls file.
Automate Macro And Lock Cells
I 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!
Can I Lock Cells After Input For Certain Users
I'm using Excel '03 and I need to be able to lock certain cells after input, but only for certain users on my network. Ideally, I (administrator), would still be able to edit the cells in case of a mistake or whatever reason. I want the other users limited to adding information and not editing the existing information in a range. I would need this applied to several columns (B, C, I, and K) if this is possible.
Lock Cells Based On Dropdown Value
I had posted a similar question on 10/27 but the thread has mysteriously diappeared. So, I am reposting my question. What I am trying to do is probably easily done using VBA. See attached workbook example.xls to follow along.
I have a dropdown (YES/NO) selection in Column C. I want to do the following when 'YES' is the selection for any row:
1) do a vlookup into the 'data' tab and return corresponding value in Column D
2) Lock the cell after the value is returned into Column D
If the selection is 'NO' then do nothing., ie., leave the cell blank and unlocked.
Lock/Protect Select Cells Only
In the attached example, I have two fields. In the first (top), I have fields I want the user to be able to enter data (revenue/weight/skids). The information at the bottom is generated by formulas that act on the user-provided data and display the results at the bottom. The key, though, is keeping Excel-ignorant users from inadvertantly destroying the formulas and data in the middle section. I still need to display the results at each step, though, so hiding those middle fields is not an option. How can I lock only some cells within a single spreadsheet like this?
Lock Cells Based On Other Cells
I have created a spreadsheet which I am trying to set up to allow users to select only 1 cell in a range and lock the rest. Each cell D5 to I5 has an assigned number 0 to 5. What I am trying to achieve is when a user clicks on a cell, the value of that cell is displayed and the remaining cells are locked. e.g. if the user selects G6, 3 is displayed and cells D6,E6,F6 and I6 are locked. The code which I am using is giving strange and unpredictable results.
Private Sub Worksheet_Change(ByVal Target As Range)
If [D5] = "0" Then
[I5].Locked = True
'Remove locked property if D5's value is anything else or is deleted.
[I5].Locked = False
'Optional, reprotect sheet
Protect/lock Cells After User Input
Have a worksheet that will be used multiple times. User will enter name and employee number only on the first instance that the worksheet is opened. Would like to protect that info automatically for further usage. Most of the worksheet is protected but some cells need to remain unlocked so the "whole" sheet protection will not work.
How do I protect only specified cells, say F3, G3, H3, I3, after the user enters data thru an input box without changing any other protected cells on the sheet?
Lock & Unlock Cells Based On Validation
I need to lock certain cells on a worksheet when a value is selected from a validation list. For example:
user goes to select an option from a validation list, that cell is now locked.
User decides to change the value with a blank option, available on the same list. the cell becomes unlocked ready for new input from the list.
the cell can only allow input when the value in the list matches that of another cell. if another cell already has different data in, it wont allow you to change the value.
Auto Lock & Protect Cells After Entry
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.
Lock Cells Based On Entry In Other Column
I 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)
Lock & Unlock A Cell Based On Another Cells Value
trying to put together a formula, I am trying enable two cells to change between being 'Locked' and 'Unlocked'. I am doing this by using a CheckBox which is referenced to cell [E16] so that when it is checked, 'TRUE' will be displayed and when Unchecked 'FALSE' is displayed. From this I have tried to devise a formula for the cells [c26:I26] and [K26] that when cell [E16] shows 'TRUE' the cells [c26:I26] and [K26] are Unlocked and when it shows 'FALSE' the cells [c26:I26] and [K26] then become locked.
Lock Cells Passed Current Date
I want to make a sheet that will lock cells from rows that have passed the yesterday date. In A column i entred the days and the locked row must have the range from A to U. After i read the post from here Lock / Protect Rows Past Due Date any solution.
Lock Cells In Range Meeting Criteria
got a problem with a little piece of code witch need to check something and if its true then its has to do 1 and they i need another if... just look at the code :P
Dim Msg, Style, Title, Help, Ctxt, Nadenken, bevestigmsg, MyString
Msg = "Heeft u alles goed ingevuld?" ' Definieert bericht.
Style = vbYesNo + vbDefaultButton2 ' Definieert knoppen.
Title = "Doorgaan?" ' Definieert titel.
Help = "DEMO.HLP" ' Definieert Help-bestand.
Ctxt = 1000 ' Definieert onderwerp
Nadenken = "test"
ActiveSheet. Unprotect Password:=Nadenken
bevestigmsg = MsgBox(Msg, Style, Title, Help, Ctxt)
If bevestigmsg = vbYes Then...................
Macro To Lock A Number Cells After Changing A Cell
I 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)
Protect Or Lock Specific Cells Independently Through Button
I have the following question: I attached my file. In sheet "boekhouding" I want every cell in column H to contain a button that locks that specific row. So I can't accidently change the dropdownmenu for that row.
I also have some sort of strange problem in worksheet "Rekeningen", Range D1-D5 is named "Grootboek" were it should be named uitgaven. I can't change the name, but it doesn't seem to matter in worksheet rekeningen (the drop down menu for uitgaven works normally), what is wrong here?
Macro To Lock Cells In Protected Sheet Depending On Criteria
I 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.
Lock Cells After Today's Date Passed (VBA Excel Code)
I am trying to lock cells after today's date has passed so that no one can make changes to it after today's date has lapsed. This is for protective reasons so that people do not remove their names from reserving something after using it. Now the code should disallow locking after cell input entry when today's date hasn't passed so that changes can still be made by the user. I am trying to determine the code to do this but I have no idea as to how to do it.
Here's a scenario: I reserve something for Aprill 11, 2009. I input my name. Since it's April 9th, 2009, I am still able to make changes up and until April 11, 2009. After this date, the cell is locked and no changes can be made, except for the administrator.
Lock/Unlock Cells Based On Criteria From Validation Lists
I'm looking for a solution to a problem and from what I've read in the forums so far, this is the place to come. I first want to say that I have tried to solve the problem by using guidance from other similar threads and adapt those answers to my needs, but as my understanding of VBA is non-existent this was somewhat difficult. I don't want to post a repeat question and I hate wasting people's time if unnecessary, but I can't do this on my own.
That being said, here is the problem;
(I have attached a simplified copy of the worksheet I am working on for reference)
I would like to lock a particular cell in my spreadsheet based upon the selections made from two drop down validation lists (Cells “C4” & “D4”)
Essentially, if a correct choice is made in both, then the cell “E4” becomes unlocked for editing. If however, the correct selection is made in one list, but not the other, I need the cell “E4” to remain locked and a message to appear to prompt the user to correct their mistake.
Furthermore, both the validation lists have a whole range of selections that are very similar but have subtle differences;
"Soft Eye by Talurit"
"Soft Eye by Splice"
Is it possible to write a VBA code that performs a similar function to the =IF(ISNUMBER(SEARCH("**",))) formula, so that if any choice that contains the word "Soft" is selected from either or both lists then the cell “E4” becomes unlocked, but if the selection does not contain that word then the cell remains locked?
If anyone could provide a code to make this happen, I would be very grateful! But please explain how to do this, as the word “Soft” won’t be the only parameter and I may need to alter the code so I need to understand the principle behind it; For example, on the spreadsheet, if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Soft Eye’ is selected then cell E4 is unlocked for editing. If in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Fused and Tapered’ is selected then cell E4 is unlocked for editing, but if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Standard Eye’ is selected then cell E4 remains locked and an error message appears to alert the user that his/her choices are incorrect. If however, "Soft" is not selected in either list then cell E4 remains locked (it has to be selected in at least one of the two lists to allow editing).
This is a work in progress (not all the formulas that will eventually be used have been entered yet, so please excuse the untidiness of it all) and the choices are likely to change based on input from my colleagues, so I need to understand how the VBA works in order to alter it accordingly, but I don’t want to put any more work into this (it’s still in the early stages) if it isn’t possible to achieve these needs.
Lock Cells, Protect Sheet With Password & Time Stamp
I have a worksheet with employees names and their overtime hours worked that week. I would like to add some code to a button that will prompt for a password. If that password matches a predefiened password then lock a range of cells and place a time stamp next to the button and maybe change the button color of the button to Red. Now if the person with the password wants to change the locked cells, they can click the button again and enter the password and the button will turn green so they can make the changes. I would like the time stamp to be updated every time the cells are locked. Also wanted to have one button for every work week so about four buttons per worksheet. Here is an example of the worksheet. The range of cells I want to lock are the ones in bold.
Use VBA To Unlock/lock A Worksheet For Data Entry?
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?