Automatically Lock Cells When Data Entered

Apr 16, 2008

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.


Automatically Lock / Protect Formula Cell When Entered

Feb 26, 2008

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?

View 2 Replies View Related

Macro To Lock Cells After Data Entered

Mar 22, 2007

I 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 View Related

Excel 2010 :: Lock Out Cells If A Value Is Entered In Another Cell

Mar 14, 2012

I 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').

View 9 Replies View Related

Excel 2007 :: Auto Lock Cells Once Data Is Entered Not Working

Aug 14, 2014

I 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

View 8 Replies View Related

Automatically Lock Cells In VBA Code?

Nov 12, 2013

I have a code which should automatically lock the cells when ever i change a certain value of cells. the code is given below. But the change doesn't happen. I've first protected the sheet then using code unlock and then again protect the sheet after locking the cells. I'm getting no errors but the cell doesn't get locked.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Then
ActiveSheet.Unprotect Password:="password"


View 5 Replies View Related

Automatically Lock & Protect Cells

Nov 27, 2006

I have a grid used as a shared diary and I need to automatically protect a cell by changing its colour to red, can this be done using VBA?

View 9 Replies View Related

How To Automatically Lock Cells Every Time Input A Value

May 6, 2004

a code that would automatically lock a cell every time I input a value to it?

View 2 Replies View Related

Automatically Lock Other Cells And Should Show Error Message?

May 2, 2012

I need a macro where user choose a value from validation, automatically lock the other cells and should show an error message.

Here is what I am looking for. User will choose Yes or no from cell H15 validation. If user choose "NO" from the validation then all other cells has to be locked in the workbook automatically except Cell H15 and a msg box needs to appear stating: "Select Yes from the validation".

View 3 Replies View Related

Lock Cells Automatically After Entering Via Macro Code

Apr 23, 2008

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.

View 2 Replies View Related

Automatically Color Cells In Relation To Data Entered

Aug 30, 2013

how 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"


View 6 Replies View Related

Lock Cell After Data Entered And Have Moved Off That Cell

Jan 5, 2013

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 Related

How To Lock A Cell Date Automatically After User Saves Changes

Oct 15, 2011

how to lock a cell date automatically after the user saves changes, it's current format it is a tick box with a reference cell Today() but it need to be locked once completed,

View 5 Replies View Related

Insert Comment Automatically When / Is Entered

Feb 1, 2013

How to make excel automatically insert a comment into a cell when a / is entered into that cell?

The comment needs to simply include the date the / was entered and nothing else.

View 8 Replies View Related

Automatically Open Comment Box When Data Is Entered.

Dec 22, 2008

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?

View 14 Replies View Related

Automatically Hyperlink Worksheets When Values Entered

Mar 6, 2014

I have an excel file that is used to record information about particular projects. We need to record dates related to these events and these are stored on separate worksheets. These use a key of sorts, where the unique references are replicated on all worksheets.

I have set up the data sheet to generate the unique identifier and the hyperlinks to the Received! and Sent! worksheets automatically as new unique references are added to the Data! worksheet.

What I would like is a VBA code to populate hyperlinks in the Received! and Sent! worksheets in column B, to link back to the Data! worksheet unique reference.

I had used formulas in the past, but the Data! worksheet is massive. I've needed to write hundreds of hyperlink formulas referencing blank spaces in the Data! sheet in anticipation of new projects. It would be better if they were populated as the sheet expanded.

View 5 Replies View Related

Current Time/date Automatically Entered Into B1 When A1>0

Jun 12, 2007

I would like to have the current time/date automatically entered into B1 when A1>0 and I want it to be non volitile, is this possible?

View 9 Replies View Related

Automatically Tick Cell If Entered Value Matches

Aug 23, 2007

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.

View 9 Replies View Related

Extend Formula Automatically As Data Entered

May 21, 2008

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
End Sub

View 4 Replies View Related

Automatically Add Multiple New Rows When Data Entered Into Sheet

Aug 18, 2014

Download the attachment and have a look.

I want there to ALWAYS be 5 FREE/EMPTY rows to be able to enter data into them I also want the black area around the outside to ALWAYS be a further 15 rows in height below that.

Sheet 1 = Simple Example
Sheet 2 = A much larger example
Sheet 3 = Erroneously added data should still extend the sheet by 5 rows each time.

View 5 Replies View Related

Excel 2007 :: Data Entered Automatically When Type Certain Things

Aug 2, 2013

I use MS Excel 2007 and I was wondering if it was possible to enter data for certain things once, and have that data copied automatically every time I type the text. For example, if I type Blue Car in a cell and type 4 in the next cell, is there a way I can program Excel to enter that data every time I type Blue Car?

View 14 Replies View Related

Lock Entered Number To A Given Number

Jun 18, 2013

I would like to be able to enter a number in cell a1. Let's say 44.2. I want that number to actually be locked in as 100'-0" in cell b1. Below in the next row I want to enter another number, lets say that one is 41.5 into cell a2. And then I want the cell b2 to return the difference in feet and inches. So ultimately the only cells that I will entering data into is in column a and column b would just return information.

The other thing I am trying to figure out is a formula for converting decimal feet to feet and inches (which I have found) but a formula that is less than 40 characters.

View 6 Replies View Related

Automatically Copy & Append Range To Another Sheet Based On Number Entered

Mar 15, 2008

I need help with creating a macro that runs when a user enters a value in the 'Numbers' column, copies and pastes data in the corresponding worksheet 'Worksheet' column by the value of 'Numbers' column data. An excel file is attached.

View 6 Replies View Related

Macro To Automatically Sort Multiple Column Groups Separately As Data Is Entered

May 20, 2014

I have a worksheet that contains multiple task lists, each having two columns, a "Priority" field and a "Description" field. The data should be sorted by Priority first and by Description second. The header row is 5 and the data is in rows 6 through 50. The first list is in A5:B50, the second C5:D50, and so on until the sixth list in K5:L50. I have a macro that works for one task list, but cannot get it to function for multiple task lists. Below is the macro that functions for the first task list. It is in the code for the sheet tab.

Option Explicit
Private Sub Worksheet_Change(ByVal Target1 As Range)
If Target1.Column = 1 Then


View 1 Replies View Related

How To Make Macro To Lock Certain Cells Connected To Another Cells Value Then Copy Daily

Mar 29, 2014

am 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

View 1 Replies View Related

Lock Cells Based On Value Of Other Cells - Code Simplification Query

Apr 22, 2009

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?

View 7 Replies View Related

Lock Empty Cells In Range Based On Other Cells Content

Apr 18, 2008

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.

View 3 Replies View Related

Lock Certain Cells By Vba

Nov 7, 2006

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.

View 2 Replies View Related

Formulas: Get Data Added In One Sheet Of A Workbook To Automatically Be Entered Into Another Sheet

Jun 26, 2006

i'm trying to get data added in one sheet of a workbook to automatically be entered into another sheet. such as a monthly, Quarterly and Annual balance sheet.

View 3 Replies View Related

When A Date Entered On A Specific Cell, Automatically Enter A Text In Other Cell

Jul 10, 2006

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 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.

View 3 Replies View Related

Lock Cells After Done With Formula?

Feb 8, 2014

I use the attached workbook to keep tack of feed use in turkeys. when a new flock is added to a farm (Airport View or Home farm) A template is copied and pasted from the "templates" sheet depending on what type of diet they will be on. (Ralco ABF or Ralco Standard) that template refers to the Ration sheet to calculate how much corn is in each diet. After a flock is complete I will never change the numbers again, it is just for record keeping. I want to be able to change the Corn/ton numbers in a ration (on the rations tab) if need be and not affect all the older completed records.

View 1 Replies View Related

Lock Dragging Of Cells

Oct 22, 2008

Is there a way to protect a sheet from a user dragging a cell?

View 6 Replies View Related

Lock Cells When Saving?

Aug 1, 2014

I have used the following code to lock cells when saving.

I need to exclude Cell B2 when saving

let me know what changes i should make.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
if ActiveSheet.Name = "Summary" Then
On Error Resume Next
'Resume to next line if any error occurs
Dim Cell As Range
With ActiveSheet
'first of all unprotect the entire
'sheet and unlock all cells
.Unprotect Password:="pw"


View 2 Replies View Related

Lock Cells On Condition

May 1, 2009

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.

View 9 Replies View Related

Lock Cells Upon Selection

Jun 20, 2006

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?

View 9 Replies View Related

Lock Cells Over Four Worksheets In One Go

Mar 12, 2007

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..

Sub Cell()
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
Exit Sub
month = InputBox("Enter Month to Lock")
ActiveSheet.Range("A1:BK22").Locked = True
MsgBox "Cells Locked", vbInformation
End If
End Sub

View 4 Replies View Related

Lock Cells For A Certain Time After Entry

Oct 10, 2012

I'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.

View 1 Replies View Related

Lock Column Cells In Table

Mar 27, 2014

I want to copy a formula and want to lock a certain column, just like you use the dollar signs in normal formulas. Because the data in tables can't be locked with dollar signs.


example 1.xlsx

View 2 Replies View Related

Lock Cells Together And Rank Rows

Sep 8, 2009

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.

View 13 Replies View Related

Protecting Cells (lock Some Collumns)

Nov 6, 2009

I would like to lock some collumns and yet change the formating on the unlocked cells mi other columns. Is there another way to protect cells other than locking cells and then protecting the worksheet?

View 5 Replies View Related

Disable Save As And Lock All Cells

Jul 10, 2013

I have a worksheet i want to disable

(1) save as function (if someone wants to saveas another name then it will ask for password

(2) lock all cells but unable autofilter (in my document if some one type in textbox given above it filter as typing....

My target to make it copyproof sheet and if someone copies my sheet than it is useless without password although user can only search books...

View 1 Replies View Related