Excel 2007 Cell Locking After Data Entered?

shawnb18 · 02-25-2008, 05:03 PM

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** · 02-26-2008, 06:28 AM

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 · 02-26-2008, 07:46 AM

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** · 02-26-2008, 03:25 PM

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.

Code:

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 · 02-27-2008, 02:20 PM

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** · 02-27-2008, 03:36 PM

Slight change to the code to allow for the various ranges

Code:

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 · 02-27-2008, 04:16 PM

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 · 10-27-2008, 04:40 PM

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

archana5 · 12-30-2008, 11:28 PM

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

02-25-2008, 05:03 PM	#1 (permalink)
shawnb18 Registered User Join Date: Feb 2008 Posts: 21 OS: XP	Excel 2007 Cell Locking After Data Entered? 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!

02-26-2008, 06:28 AM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,482 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel 2007 Cell Locking After Data Entered? 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

02-26-2008, 07:46 AM	#3 (permalink)
shawnb18 Registered User Join Date: Feb 2008 Posts: 21 OS: XP	Re: Excel 2007 Cell Locking After Data Entered? 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!

02-26-2008, 03:25 PM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,482 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel 2007 Cell Locking After Data Entered? 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. Code: 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

02-27-2008, 02:20 PM	#5 (permalink)
shawnb18 Registered User Join Date: Feb 2008 Posts: 21 OS: XP	Re: Excel 2007 Cell Locking After Data Entered? 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!

02-27-2008, 03:36 PM	#6 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,482 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel 2007 Cell Locking After Data Entered? Slight change to the code to allow for the various ranges Code: 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

02-27-2008, 04:16 PM	#7 (permalink)
shawnb18 Registered User Join Date: Feb 2008 Posts: 21 OS: XP	Re: Excel 2007 Cell Locking After Data Entered? 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.

10-27-2008, 04:40 PM	#8 (permalink)
mlrolling Registered User Join Date: Oct 2008 Posts: 3 OS: xp	Re: Excel 2007 Cell Locking After Data Entered? I wanted to know how would I go about locking a cell after it has been changed.

12-30-2008, 11:28 PM	#9 (permalink)
archana5 Registered User Join Date: Dec 2008 Posts: 1 OS: Xp	Re: Excel 2007 Cell Locking After Data Entered? 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..