Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 22

Thread: Auto lock cells after data entered and SAVED.

  1. #1
    Join Date
    20th January 2012
    Posts
    25

    Auto lock cells after data entered and SAVED.

    Firstly, I know 0 about programming or VBA, but I found this code which auto locks cells after you enter data in them.

    VB:
    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 
    
    

    Now I want to make this only work when the file is saved. For example, when an employee opens up the file he or she is able to enter any new data, but not edit old data, and once saved, they will not be able to edit the data they just entered.
    I hope this makes sense.
    Thanks!
    Last edited by AAE; January 28th, 2012 at 22:50. Reason: add code tags

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2012
    Posts
    31

    Re: Auto lock cells after data entered and SAVED.

    Hi rosh22

    you need to put this code in Workbook's beforesave event:

    VB:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
        Dim Cell As Range 
        With ActiveSheet 
            .Unprotect Password:="" 
            .Cells.Locked = False 
            For Each Cell In ActiveSheet.UsedRange 
                If Cell.Value = "" Then 
                    Cell.Locked = False 
                Else 
                    Cell.Locked = True 
                End If 
            Next Cell 
            .Protect Password:="" 
        End With 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    20th January 2012
    Posts
    25

    Re: Auto lock cells after data entered and SAVED.

    Thanks for the quick reply.

    So I put this code in the workbook save event area, but when I arrive there, there is already this code there.

    VB:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
         
    End Sub 
     
    Private Sub Workbook_Open() 
         
    End Sub 
    
    
    So where do I put it? Also, do I still need to put the original code in the worksheet area?
    Last edited by AAE; January 28th, 2012 at 22:49. Reason: add code tags

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2012
    Posts
    31

    Re: Auto lock cells after data entered and SAVED.

    Those are just junk lines which are automatically created whenever you go to workbook's event area.
    When you go to BeforeSave event area of Workbook, delete all those lines which get auto generated and make your editor completely blank, then you can paste the above code which I posted.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    20th January 2012
    Posts
    25

    Re: Auto lock cells after data entered and SAVED.

    Thanks again for the quick reply.

    So I left my original code in the worksheets VBA, and then entered your code in the workbook's event area, but I am still having the same problem. It is still auto locking after each entry, even before I press save.

    Any ideas?

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2012
    Posts
    31

    Re: Auto lock cells after data entered and SAVED.

    pls attach your workbook, (if this dosen't contain any sensitive data)

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    20th January 2012
    Posts
    25

    Re: Auto lock cells after data entered and SAVED.

    My workbook.xlsMy workbook.xls

    Ok

    So you see the rows starting with date.

    I need a system where once you fill up a cell, and save the file, you cannot edit the cell again, unless you have a password of course.

    Thanks so much!

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th January 2012
    Posts
    31

    Re: Auto lock cells after data entered and SAVED.

    Hi, please see the attachment. it's working perfect on my end. I have entered one row for testing and checked it's working fine.
    VB:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
        On Error Resume Next 
         'Resume to next line if any error occurs
        Dim Cell As Range 
        With ActiveSheet 
             'first of all unprotect the entire
             'sheet and unlock all cells
            .Unprotect Password:="" 
            .Cells.Locked = False 
             'Now search for non blank cells
             'and lock them and unlock blank cells
            For Each Cell In ActiveSheet.UsedRange 
                If Cell.Value = "" Then 
                    Cell.Locked = False 
                Else 
                    Cell.Locked = True 
                End If 
            Next Cell 
            .Protect Password:="" 
             'Protect with blank password, you can change it
        End With 
        Exit Sub 
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    20th January 2012
    Posts
    25

    Re: Auto lock cells after data entered and SAVED.

    Thanks so much! One more question, is there a way to make this only apply to a certain sheet in my workbook?

    Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th January 2012
    Posts
    31

    Re: Auto lock cells after data entered and SAVED.

    Try this, you can change sheet name as per your convenience

    VB:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
         
        If ActiveSheet.Name = "Sheet1" Then 
            On Error Resume Next 
             'Resume to next line if any error occurs
            Dim Cell As Range 
            With ActiveSheet 
                 'first of all unprotect the entire
                 'sheet and unlock all cells
                .Unprotect Password:="" 
                .Cells.Locked = False 
                 'Now search for non blank cells
                 'and lock them and unlock blank cells
                For Each Cell In ActiveSheet.UsedRange 
                    If Cell.Value = "" Then 
                        Cell.Locked = False 
                    Else 
                        Cell.Locked = True 
                    End If 
                Next Cell 
                .Protect Password:="" 
                 'Protect with blank password, you can change it
            End With 
            Exit Sub 
        End If 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 7
    Last Post: July 21st, 2010, 03:57
  2. Automatically Lock Cells When Data Entered
    By bhg463 in forum EXCEL HELP
    Replies: 4
    Last Post: April 16th, 2008, 22:10
  3. Lock Cells In Range On Value Entered
    By PhiLLaY in forum EXCEL HELP
    Replies: 8
    Last Post: October 17th, 2007, 20:31
  4. Macro To Lock Cells After Data Entered
    By pranabksingh in forum EXCEL HELP
    Replies: 4
    Last Post: March 22nd, 2007, 19:30
  5. Auto Lock Cells
    By Gops in forum EXCEL HELP
    Replies: 4
    Last Post: January 31st, 2005, 07:35

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno