Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default LOCK CELL AFTER DATA IS ENTERED

Hi
Is there a way to automatically lock a cell after a value is entered?

Thanks for the help,
Mike
  #2   Report Post  
Posted to microsoft.public.excel.misc
ed ed is offline
external usenet poster
 
Posts: 82
Default LOCK CELL AFTER DATA IS ENTERED

On Oct 19, 10:24 am, MIke wrote:
Hi
Is there a way to automatically lock a cell after a value is entered?

Thanks for the help,
Mike


You can Copy the cell and then Paste Special / Values, and then
Format the cell to be Locked and then Protect the Worksheet, but
that's hardly automatic.

ed

  #3   Report Post  
Posted to microsoft.public.excel.misc
RCW RCW is offline
external usenet poster
 
Posts: 9
Default LOCK CELL AFTER DATA IS ENTERED

To see how this would work, open a new workbook. Highlight cells A1:A20 and
use Format-Cells-Protection tab and UNCHECK the locked box.

Right click the Sheet Tab and select View Code and then paste in this
subroutine.

Use Tools-Protection-Protect Sheet to assign the password 'thepassword'

You can only add data to to cells A1:A20 and once you add data to any of
those cells, the cell becomes locked preventing any further modification.

Modify the range and password in the subroutine to suit your application.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

' Place this code in the worksheet code module.
' The subroutine unprotects the sheet and IF an entry is made
' in an empty ("") cell, the cell is locked and then the
' sheet's protection is turned back on. Any further
' attempts to edit the cell generate the password msgbox.
' You can set the range to one cell ("A1") or an area ("A1:Z300").
' 1. Use Format - Cells - Protection to unlock the cells
' in the range where one time entries are to be allowed.
' 2. Protect the worksheet with the same password as
' you use twice in the following subroutine (thepassword).


On Error GoTo justenditall

Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Value < "" Then
ActiveSheet.Unprotect Password:="thepassword"
Target.Locked = True
End If
End If

ActiveSheet.Protect Password:="thepassword"

justenditall:
Application.EnableEvents = True
End Sub


"MIke" wrote:

Hi
Is there a way to automatically lock a cell after a value is entered?

Thanks for the help,
Mike

  #4   Report Post  
Posted to microsoft.public.excel.misc
joe joe is offline
external usenet poster
 
Posts: 2
Default Quick question....

If there a way to only lock the cells after the user had saved the file? I tried your macro and it work really good, too good. :)

The thing is, if a user inputs the wrong data he/she will not be able to go back and make the necessary changes. Is there a way to allow the user to verify thier data entry before locking the cell?

Thanks in advance

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Quick question....

Post in the original thread.

Do not start a new one with any details whatsoever.

Whose macro? What does it do?

You could lock cells as the user saved the file using a BeforeSave event.

Yes, you can verify if the data is correct before locking the cell but what
would constitute "correct data"?


Gord Dibben MS Excel MVP


On Tue, 23 Oct 2007 15:56:10 -0700, joe wrote:

If there a way to only lock the cells after the user had saved the file? I tried your macro and it work really good, too good. :)

The thing is, if a user inputs the wrong data he/she will not be able to go back and make the necessary changes. Is there a way to allow the user to verify thier data entry before locking the cell?

Thanks in advance

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I lock unclock cells to protect the data entered? Sandi@hotmail Excel Worksheet Functions 1 March 13th 07 03:46 AM
data changes when entered into a cell Ian Gill Excel Worksheet Functions 1 October 10th 06 05:39 PM
Lock Data in Cell after entered CrimsonPlague29 Excel Discussion (Misc queries) 0 May 9th 06 11:51 AM
How do I lock a cell automatically after it has data entered. Dandy Excel Discussion (Misc queries) 0 March 30th 06 12:39 PM
how can i lock cell immediately if any value Entered? Sureshsmartdc New Users to Excel 2 August 20th 05 11:05 PM


All times are GMT +1. The time now is 10:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"