Closed Thread
Results 1 to 12 of 12

Protect/Lock Cells after Data Input

  1. #1
    Registered User
    Join Date
    02-09-2011
    Location
    Durban, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    23

    Unhappy Protect/Lock Cells after Data Input

    Hi,

    I have a worksheet where I require, that once employees enter data into cells in columns labelled 'Attendant', cells cannot be ammended thereafter. There are 3 such columns on the worksheet labelled '01 March'.
    I've messed around with some code but i'm getting nowhere...attached is a sample

    Thank You for Your Help
    Attached Files Attached Files
    Last edited by Ashraf1; 03-02-2011 at 04:34 PM.

  2. #2
    Registered User
    Join Date
    02-27-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    40

    Re: Protect/Lock Cells after Data Input

    Hi,

    I hope I understand what your on about. Also, you have some fine code in there for other things.

    The code below protects the cell from change once an entry has been made in column I, I have also included a test workbook, be sure to enable macros.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by MrExcelPro; 02-28-2011 at 04:36 PM.
    Regards
    MrExcelPro

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Protect/Lock Cells after Data Input

    This code will lock the cell after it is changed
    Please Login or Register  to view this content.
    You must make sure all cells to be completed are Unlocked first
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    02-09-2011
    Location
    Durban, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Protect/Lock Cells after Data Input

    Hello Mr ExcelPro,

    Your code works perfectly on the sample, however I'm struggling to get it to work in conjunction with the other codes in the workbook, when I paste it. The other code just stops working or your code gets messed up. I'm obviously not doing something right but I have no clue as to what it is. I have enabled macros.
    Is it possible for you to introduce your code into the attached workbook which I can then use as a final copy? I'm, embarrasingly, asking you to do it for me!

    PS..I'm a complete NOVICE...the 'fine' code u noticed is courtesy of DONKEYOTE

    Thank You for Your Help
    Attached Files Attached Files

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Protect/Lock Cells after Data Input

    I've slightly amended my code & added it for you
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-09-2011
    Location
    Durban, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Protect/Lock Cells after Data Input

    Thank You,

    I would also need the code to work for columns U & AG.
    Could you possibly amend this?

    Thank You

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Protect/Lock Cells after Data Input

    Why not
    a) State requirements fully in the original question
    b) Try to understand the code & amend it yourself. The Forum is to help you learn, not provide fully functioning programs

    Here's amended code
    Please Login or Register  to view this content.
    Last edited by royUK; 03-01-2011 at 07:02 AM.

  8. #8
    Registered User
    Join Date
    02-09-2011
    Location
    Durban, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Protect/Lock Cells after Data Input

    Thats understandable...

    Thank You and sorry if I've annoyed you

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Protect/Lock Cells after Data Input

    You haven't annoyed me, see the new code

  10. #10
    Registered User
    Join Date
    02-27-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    40

    Re: Protect/Lock Cells after Data Input

    Hello Again,
    I have amended the workbook so it nows works on all 3 columns. The "password" can be changed to what you like to protect the sheet, you should also consider protecting the VBA code as the users who use it can view it and get the password.

    Excel>alt+f11>Tools>VBA Project Properties>Protection TAB

    There is also a second bit of code that you can run to reset all protections on the sheet

    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    08-26-2014
    Location
    Saudi Arabia
    MS-Off Ver
    2010
    Posts
    1

    Re: Protect/Lock Cells after Data Input

    Hi,

    can you please modify the below coding for protecting multiple cells LIKE "I" & "L"


    Please Login or Register  to view this content.
    Last edited by Fotis1991; 08-27-2014 at 04:22 AM. Reason: added code tags

  12. #12
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Protect/Lock Cells after Data Input

    cyberangel83

    Welcome to the forum. Pls take some minutes to read forum rules and then post your question in another new thread, because:


    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

Closed Thread

Thread Information

Users Browsing this Thread

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

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