# Excel 2007 Cell Locking After Data Entered?



## shawnb18 (Feb 25, 2008)

So this is a 2 part question...

I am working on making an electronic time card for my company and I want the office girls to be able to type a preset word (=clock) to electrnoically time stap the card (which I have working fine) but I only want them to be able to input =clock and if anything else is entered for it to not accept it and once they type in =clock i also want it to where the cell will lock so they can't manually change the figure afterwards.


Part 1: Is it possible to limit the access of the data within a cell? I only want the person accessing the spreadsheet to be able to enter the value "=clock" into a given cell.

Part 2: Is it possible to lock a cell where it cannot be modified after someone initally puts something in the cell? So once they type "clock" the cell blocks any other access so they can't go back the next morning and type in =clock to make up for the day they may have been late on before.

Before it comes up, yes we have a old fashioned time clock but I am trying to get us out of the stone age.

Thank you in advance!


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome to TSF.

You can limit user input via Data Validation. On the menu bar Data > Vaildation - you can set whatever limits suit.

Yes you can do the second part using VBA code. I would use a Worksheet Event to test that you have the correct cell and the correct input and then lock the cell.


----------



## shawnb18 (Feb 25, 2008)

Thank you for your reply! How would I use VBA? I have never used it... If I can get the basics I can probably figure it out. Thank you!


----------



## Glaswegian (Sep 16, 2005)

Hi

Here's a simple example. Note that locking a cell will only have an effect if the worksheet is protected. This example checks whether or not cell D5 has is empty or not. If empty the code exits and nothing happens. If there is a value in the cell, the cell property is changed to locked and the sheet is re-protected, thus ensuring that users cannot change D5.

Right click on the tab of sheet1 and select 'view code' - then copy and paste the code in the right hand pane. Then make sure D5 and a couple of other cells are unlocked, protect your sheet *without* a password and try it out. You can include a password if you want - it would just be a parameter after the Unprotect or Protect commands.

```
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$D$5" Then Exit Sub

Sheets("Sheet1").Unprotect
If Target.Value <> "" Then
    Target.Locked = True
End If
Sheets("Sheet1").Protect
End Sub
```
Post back if you have any problems or questions.


----------



## shawnb18 (Feb 25, 2008)

Thank you! That works well. How can I do it for multiple cells?

F9 thru F15 and F21 thru F27 same cells with collums G,H and I.

Thank you again!


----------



## Glaswegian (Sep 16, 2005)

Slight change to the code to allow for the various ranges

```
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F9:F15,F21:F27,G9:G15,G21:G27,H9:H15,H21:H27,I9:I15,I21:I27")) Is Nothing Then Exit Sub
    Sheets("Sheet1").Unprotect
        If Target.Value <> "" Then
            Target.Locked = True
        End If
Sheets("Sheet1").Protect
End Sub
```
Change your sheet names to suit.


----------



## shawnb18 (Feb 25, 2008)

That worked perfectly! Thank you for your help. I have another excel question but I will search for it first and then post a new thread if I can't find it so for easy search next time. Thank you again.


----------



## mlrolling (Oct 27, 2008)

I wanted to know how would I go about locking a cell after it has been changed.


----------



## archana5 (Dec 31, 2008)

HI I have a similar question...I am new to VB and i need a code achieve the following table in Excel.
I have a table with columns A,B,C
If data is entered in cells of column A,Cells in columns B and C Should get locked.
If data in the cells of column A is deleted,then cells in columns B,C should be unclocked.

PLz ..PLz..NEEd an urgent help with this...my boss needs this immediatly..can any plzzzzzzzzzzz help me on this. asap...plzz..:sigh:


----------

