Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
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?

View Replies!   View Related
Automatically Lock & Protect Cells
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 Replies!   View Related
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?

View Replies!   View Related
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?


View Replies!   View Related
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.

View Replies!   View Related
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)

View Replies!   View Related
Protect Some Cells While Allowing Entry In Others
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 Replies!   View Related
Auto Copy Template On Record Entry & Link Set Cells Back New Record Entry Sheet
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 Replies!   View Related
Lock And Protect In XL97
Can I lock cells and protect the sheet and prevent the user to select locked cells in xl97?

View Replies!   View Related
Lock/Protect Single Module
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 Replies!   View Related
Lock / Protect Rows Past Due Date
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 Replies!   View Related
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?

View Replies!   View Related
Lock Cell After Entry
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 Replies!   View Related
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?

View Replies!   View Related
Lock A Row With An Entry In Col B On Saving Or Closing Workbook
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 Replies!   View Related
Increment Cell On Selection And Lock/Protect From Re-Selection
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 Replies!   View Related
Auto Entry For Sorting / Saveing
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 Replies!   View Related
Cell Entry Auto Sum
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 Replies!   View Related
Auto Fill Off Of Zip Code Entry
If I have 3 fields: zip code, city and state; is there a way where I can have the user of my form enter their zip code and it will populate both city and state. I have the data, I just dont know exactly how to code it.

View Replies!   View Related
Auto Color Change With Data Entry
Is there a way to make a cell change its fill color automatically whenever data is entered into that cell?

View Replies!   View Related
Auto Date Entry In Adjacent Cell
formula to auto input the date into a specific cell once one cell has been changed

View Replies!   View Related
Debug Assistance - Auto Date Entry
i try to lock cells in the area the VBA code affects. My goal is to actually have affected cells by this VBA code lock immediately after anything is entered in the affected cells. Area needing Debugging is in the If - Else portion. My worksheet will be protected.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LLoop As Integer
Dim LTargetRange1 As String
Dim LDestRange1 As String
LLoop = 10
While LLoop <= 1000
'Link column B to A
LTargetRange1 = "B" & CStr(LLoop)
LDestRange1 = "A" & CStr(LLoop)
If Not Intersect(Range(LTargetRange1), Target) Is Nothing Then
If Len(Range(LTargetRange1).Value) > 0 Then
Range(LDestRange1).Value = Date
Else
Range(LDestRange1).Value = Null
End If
End If
LLoop = LLoop + 1
Wend
End Sub

View Replies!   View Related
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?

View Replies!   View Related
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.

View Replies!   View Related
Auto Fill Doesn’t Work, But Trying To Avoid Manual Entry Of Rows.
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)

Above is the formula I am working with. I am inserting it into row 4 thru row 996 in a number of different columns. The auto fill function works great for this part of the formula….

=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!

However, this part Z29:Z39 I have to enter manually row by row until I can figure out a better way. Do you know an easier way?

To put this formula…
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
Into any column row 4 thru row 996, without having to change Z29:Z39 for every row, since I cannot rely on autofill?

View Replies!   View Related
Protect VBA Project Or Lock Project For Viewing By VBA
I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.

View Replies!   View Related
Lock Dragging Of Cells
Is there a way to protect a sheet from a user dragging a cell?

View Replies!   View Related
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.


View Replies!   View Related
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?

View Replies!   View Related
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.

View Replies!   View Related
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..

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

View Replies!   View Related
How Do You Protect Cells On Condition Of Other Cells Having Data Inputted
way of protecting an array of cells on the condition that information is entered in another array of cells or vice versa. So, put simply, if data goes into a1 then a2 becomes protected (or vice versa).

View Replies!   View Related
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.

View Replies!   View Related
Post To Lock Cells With Data
I 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 Replies!   View Related
Protecting Cells (lock Some Collumns)
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 Replies!   View Related
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!

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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
ActiveSheet.Unprotect ("PASSWORD")
[I5].Locked = True
ActiveSheet.Protect ("PASSWORD")
'Remove locked property if D5's value is anything else or is deleted.
Else
ActiveSheet.Unprotect ("PASSWORD")
[I5].Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If............................

View Replies!   View Related
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
ActiveSheet.Unprotect Password:="1234"
Target.Locked = True
ActiveSheet.Protect Password:="1234"
End If
End Sub

View Replies!   View Related
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 Replies!   View Related
Lock Used Cell. Unlock Blank Cells
I need to lock cells or ranges in a worksheet which has value (any value)....
The cells which are blank should be unlocked so that the users can enter data.

View Replies!   View Related
Macro To Lock Cells After Data Entered
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 Replies!   View Related
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.

View Replies!   View Related
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

Sub Bevestig3()
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...................

View Replies!   View Related
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)


View Replies!   View Related
Lock/unlock Cell Based On Another Cells Input
I want cell g12 to be locked unless cell h7 is Grass Fire or Timber Fire (H7 is a drop down validated cell)

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved