Auto Lock & Protect Cells After Entry

Feb 28, 2007

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.

ADVERTISEMENT

How To Auto Lock A Cell After Entry

Aug 27, 2012

I'm creating an inventory of cars for a car dealership that has about 27 or so columns and 500 rows for entries. Currently there are 50 rows used to keep track of inventory and with the additional rows for future entries. I want to set it up where the data entered into the cells gets locked automatically when saved and reopened for future use and can only be altered if a password is entered. Table is C3:AE503.

View 5 Replies View Related

How To Auto Lock Cell After Data Entry

Jul 31, 2012

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

Lock/Protect Select Cells Only

Apr 17, 2008

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?

View 2 Replies View Related

Protect / Lock Cells After Data Input?

Mar 15, 2013

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

Protect/lock Cells After User Input

Jan 9, 2009

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?

View 3 Replies View Related

How To Lock / Protect Cells In Different Columns Based On Date

Oct 26, 2013

Trying to incorporate the following in a worksheet:

Lock the cells automatically in the columns that are older than 2 days or more

Lock the cells corresponding to today...

View 2 Replies View Related

How To Lock / Protect The Cells In Different Columns Based On Date

Oct 25, 2013

Trying to incorporate the following in a worksheet: Lock the cells automatically in the columns that are older than 2 days or more Lock the cells corresponding to today and yesterday using a command button after entering X Copy the sheet multiple times with the same features

View 14 Replies View Related

Protect Or Lock Specific Cells Independently Through Button

Apr 20, 2008

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?

View 11 Replies View Related

Lock Cells, Protect Sheet With Password & Time Stamp

Jul 18, 2007

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.

View 3 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

VBA To Lock Cells After Data Entry And Save

Aug 13, 2013

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

Lock Cells Based On Entry In Other Column

Mar 30, 2007

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)

View 4 Replies View Related

Protect Some Cells While Allowing Entry In Others

Aug 9, 2008

Iam making some test report tamplet`s, in that when I select the material from drop down (ref enclosed xls file) all spec will be displayed. I want the sheet to be protected at the same time few cells to be allowed for data entry (cells marked with blue) If sheet can`t be protected I want to hide the formulas and spec details used in it.

View 2 Replies View Related

Auto Copy Template On Record Entry & Link Set Cells Back New Record Entry Sheet

May 26, 2009

I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.

1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)

2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.

3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab

View 6 Replies View Related

Excel 2010 :: Auto Lock Selected Range Of Cells After Saving?

Jul 17, 2014

I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.

While all of the above activity is going on, new customers are continuously being in added in G4, G5....

Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010

[URL]

View 1 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

Excel 2010 :: Auto Lock Selected Range Of Cells After Saving

Jul 17, 2014

I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.

While all of the above activity is going on, new customers are continuously being in added in G4, G5....

Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.

I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...

Auto lock cells after data entered and SAVED.

Auto Lock Selected Range of Cells After Saving

I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.

This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.

View 1 Replies View Related

Lock Certain Cells To Allow Data Entry But Not Allow Data To Be Removed

Apr 23, 2013

is there a way to lock certain cells to allow data entry but not allow data to be removed.

View 2 Replies View Related

Lock And Protect In XL97

Oct 24, 2008

Can I lock cells and protect the sheet and prevent the user to select locked cells in xl97?

View 9 Replies View Related

Protect Or Lock Workbook BEFORE Certain Date

Jul 7, 2012

How to protect or lock their spreadsheets after certain date.

My question is how to protect or lock workbooks before certain date?

Example:

I have individual woorkbooks for each week in a calendar year saved in one folder.

Is there any way that I can protect them from either being opened or if opened than not be able to save anything in them until one week before the calendar week they are designed for (or whatever date I wish to set)?

View 3 Replies View Related

Lock/Protect Single Module

Aug 21, 2006

I have a workbook(1), contains a worksheet that once filled with user entered parameters, generates data through a VBA module (macro). This module(1) is a proprietary algorithm, and the author has locked the VBA project to protect the module from being tampered with/modified (and rightly so).

I take this data and use it as input in my own algorithms to generate more data. By locking "VBA project" in that workbook(1), I can't see anyway to add my own module(2) to that particular workbook(1). I can create another workbook(2), and write a module(2) there that reads the data from the first workbook(1), but then I'm left with TWO workbooks to do a single task, both have to be opened, and its just not a very elegant way of doing things.

I've contacted the author of the proprietary module(1), and he has no problem modifying the lock protection in the workbook(1) so that only his module(1) is locked, but VBA is not really his forte, and he doesn't know how to do it, or if it is even possible. I've checked many Excel Help facilities, and could not find an answer.

1. Is there any way to lock a SINGLE VBA module within a workbook?

2. Also, is there only ONE VBA project allowed per workbook? If I could add an unprotected VBA project, I could add my own module.

I may not be understanding the entire VB architecture properly, I'm using manuals from 1993, and On-line MS Help is not that "help"ful, so any assistance would be greatly appreciated. Mainly I just want to find out if this is possible or if its not, in which case I'll be forced to work around it in a inelegant fashion. It just seems logical that you should be able to lock just one module.

View 7 Replies View Related

VBA Hide Rows - Can't Lock To Protect Formulas

Jun 6, 2012

I have a SS that puls data from another sheet using the following formula

=IF(OR('Site Visit ASSESSMENT'!D9={"PARTIAL COMPLIANCE","NOT COMPLIANT","NOT ASSESSED","ADDITIONAL INFO NEEDED"}),'Site Visit ASSESSMENT'!A9,"")

I also have VB Code in teh sheet as follows to hide rows. If I protect colum A and protect sheet I get an error. How can I protect the sheet and still have the following commnad work?

Private Sub CommandButton1_Click()
'Toggle rows hidden where cells in column D are blank
If ActiveSheet.AutoFilterMode Then

[Code].....

View 6 Replies View Related

Lock And Protect Worksheet After A Date In Cell

Feb 1, 2013

I have a worksheet that is protected with data entry cells unlocked.

1. Unprotect worksheet
2. Lock all cells in the worksheet and then protect.
3. After a date in a cell in the worksheet.

I'm a newbie to vba and at least know vba is the only way. And this need to be automated.

View 1 Replies View Related

How To Lock Or Protect Cell Using Data Validation

Jun 6, 2013

1- If I wish to write in the cell I can do it ?
2- When I do not wish to write in the cell I can do it (Its meaning protect the cell.)

View 2 Replies View Related

Lock / Protect Rows Past Due Date

Mar 5, 2008

I have a workbook list of activities that I am hoping will visualise the scheduling of routine maintenance tasks in my engineering department. The date of the most recent check is entered in row 6 and the interval in row 5. Hence the number 7 denotes a weekly check. The conditional format is such that the reds indicate that a check needs to be carried out. If the check is done on the scheduled day then a "Y" is entered into the square. This will then turn the square green. If "Y" is entered on any other day then the square turns orange to indicate the check is done, if not on time. The red squares do not change colour unless a yes is entered. This is designed to show the history of checks and give us the opportunity to show compliance to routines. What I need to do is lock the rows that have passed the current date, to prevent unscupulous modification. Thus the macro would look down column A and if the date is less than today() would lock the row. I have truncated the file as much as I can. A nice to have would be the facility for an adminstrator to go in (perhaps password protected) to make changes if necessary.

View 3 Replies View Related

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

Lock Cell After Entry

Mar 14, 2007

I am using the code below to prompt the user to enter his/her name in cell B4. What I would like to do is have it so that after the user enters his/her name, that cell is locked and cannot be cleared.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$4" Then
Range("B4").Value = InputBox("Please Enter " + Range("a4"), "Company")
End If
End Sub

View 9 Replies View Related

Lock A Row With An Entry In Col B On Saving Or Closing Workbook

Feb 15, 2009

I have a spreadsheet for recording and summarising particular transactions. Column B records the date and Col C details the transaction type (from a data valdation list). Using conditional formatting the cell (in the same row) to whch the transaction value is to be entered is highlighted.

Other cells in the row contain formulas and are locked and the sheet is password protected.

On saving or closing the workbook I want to lock all the cells (used or unused) in any row in which there is a date in column B. How can I do this?

View 11 Replies View Related

Use VBA To Unlock/lock A Worksheet For Data Entry?

Oct 16, 2009

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?

View 9 Replies View Related

Protect Excel Column After Entry

Dec 19, 2012

I want to lock column as and when your fill his report

E.g. in column C user filled report on 1st of this month

On second day column C must be locked or protected

User must enter in D column only

View 11 Replies View Related

VBA To Auto Lock A Cell

Apr 18, 2012

I have a range of cells I want to become locked (thus, uneditable), based upon the value of another cell.

For example, if cell A1 is "True," (based on a drop-down, not a formula) I want cells B1:B10 to become locked. I want the user to be able to edit these cells only when A1 is false. Also, the sheet is already passsword protected.

View 2 Replies View Related

Lock Column Auto Sort

Jul 6, 2013

is there any way to automatically sort a column from A-Z without need to choose column, sort&filter, a->z? maybe a pivot table?

View 1 Replies View Related

Cell Auto-lock After Entering Time

Sep 16, 2013

I have created a time sheet and I would like to know if there is a way to auto-lock cells after time is entered so that it cannot be changed. As of right now I have employees enter the time by using Shift+Ctrl+;, so the exact time is recorded, however employees can write in whatever time they want. so I guess what I want to do is 2 things:

1- can the cell be formatted to only accept Shift+Ctrl+;, so that the exact time is entered in the cell and prevent employees from entering times they were not at work?

and

2- can the cell auto-lock after Shift+Ctrl+; is entered so that they can't modify the time sheet the next day if they arrived earlier than the day before?

View 5 Replies View Related

Increment Cell On Selection And Lock/Protect From Re-Selection

Oct 3, 2006

I tried very hard to design a leave roster for user to mark their leave application. The criteria are as follows:

1. 4 applications per date
2. a region of worksheet (i.e. "A2 to H20) will be defined for users to "click" on the cells (within the defined region) to mark their application.
3. once a cell is clicked (i.e. marked), it cannot be altered.
4. each click will automatically increase the total by 1.

I got the following code to deal with the situation but user can still click on any cell outside the region (in fact I fail to define the region).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m
ad = Mid(ActiveCell.Address, 2, 1)
m = Range(ad & 24).Value 'here a formula "CountA(A2,A20)" will be place in the cell (24, c).......................

View 2 Replies View Related

Auto Hide And Protect Worksheets Before Closing

Nov 15, 2011

I wrote the following code to hide & password protect an excel sheet :

[Code] .....

How could I alter it so that it hides and protects this sheet automatically before closing the workbook ?

View 3 Replies View Related

Protect VBA Project Or Lock Project For Viewing By VBA

Apr 27, 2007

I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.

View 9 Replies View Related

Excel 2007 :: Auto Lock Rows 6 Days After Date Passed

Jul 30, 2014

I am new to Excel programming, haven't done any before and not familiar with creating/editing macros. Using Excel 2007.

I would like to automatically lock all the cells in a row if the date in column A in that row is >= 7 days old. Data is entered once a week (some time Sunday - Saturday) and I would like historical data (rows) to be locked so that they can't be edited inadvertently.

If possible could I also highlight the current working week somehow?

I have attached the worksheet : testworksheet.xlsx

View 3 Replies View Related

Cell Entry Auto Sum

Mar 1, 2009

What could be the formula if i want a particular cell to auto sum the data input. For example if i click cell a1 and entered a number and again entered a number , the previous entry will be added to recent entry.

View 9 Replies View Related

Auto Entry For Sorting / Saveing

Apr 24, 2009

I'm running a macro to auto sort data and copy it to another page. One of the sort parameters is to only look at the data that has a blank in column 13. The code is as below:

View 2 Replies View Related

ADVERTISEMENT