+ Reply to Thread
Results 1 to 7 of 7

Auto lock cells after data entry when file is saved

  1. #1
    Registered User
    Join Date
    10-02-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    35

    Auto lock cells after data entry when file is saved

    I have a VB code that auto locks cells after data is entered when the file is saved, however I would like to maintain the Autofilter function on the locked sheet.
    Normally when you Protect a Sheet you can 'Allow all users of this worksheet to:' - Use AutoFilter.
    However the code that I am using (found out in cyberspace) just simply locks all cells with data in the named range. Is there something that I can add to the attached code to allow the use of AutoFilter once the named range has been protected?

    Please Login or Register  to view this content.

  2. #2
    Registered User
    Join Date
    10-02-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Auto lock cells after data entry when file is saved

    Have I stumped everyone?

  3. #3
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Auto lock cells after data entry when file is saved

    try:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    10-02-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Auto lock cells after data entry when file is saved

    what part of ThisWorkbook code in VBA do I put this? Where would I insert it in my code above?

  5. #5
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Auto lock cells after data entry when file is saved

    I've never used .Parent.Unprotect so rather than mess you around with me trying to guess the correct format for that......try this instead:


    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-02-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Auto lock cells after data entry when file is saved

    It gives me the error "Compile Error"

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-02-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Auto lock cells after data entry when file is saved

    Ok, I'm open to a complete redesign of the code if it will work a better way....
    Here is what I'm looking for the sheet to do...
    1) Allow users to enter data in blank cells in the table
    2) Auto protect cells that have data once the file is closed. (Password can be blank or 'password' as this is just a deterrent to help keep people from messing up the file. They can unlock if needed to edit data already entered but at least by unlocking the sheet they are aware of what they are doing)
    3) If the user hits TAB at the last entry the table creates a new line.
    4) Don't auto protect the top 3 rows
    5) allow the auto filter to work when the sheet is protected.

    I'm almost there..but right now the Auto Filter doesn't work on a protected sheet, and the table doesn't automatically add a new line when TAB is hit if the sheet is protected.

    I know I'm over my head, but I have had some excellent help from this forum in the past.
    See the attached file....
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Auto lock cells after data entered and SAVED
    By fedcco in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-26-2016, 07:40 AM
  2. Macro to auto lock cells after data entry
    By Mhofu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-06-2013, 05:45 AM
  3. [SOLVED] Lock All Cells after data entry of a specific cell
    By celialynn in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-09-2012, 01:41 AM
  4. lock cells after data entry
    By aleenkhan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-06-2012, 03:14 AM
  5. Auto Lock Cells after Data Entry AND Save
    By ROSH22 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2012, 06:57 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1