Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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.

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

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

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

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