+ Reply to Thread
Results 1 to 7 of 7

Automatically Protect a single cell

  1. #1
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Exclamation Automatically Protect a single cell

    Hi All!

    I have a worksheet1 and want the users (employees) to enter the data in it, but if they enter any value that must be locked itself, so that they can't delete the data.

    Can you plz please test my below codes and make the required corrections in it?
    1) How can I add UserInterFaceOnly:=True in the protection, so they are just allow to format the cell/ cells, but can't delete anything?
    2) If I select more than one cell (range) whether they are blank or not, but my codes give an error msg Run-time error '13': Type mismatch

    Please Login or Register  to view this content.
    MS Workbook 2007
    Kindly find the attached for more information.
    Attached Files Attached Files
    Last edited by SunOffice; 03-15-2011 at 11:47 PM.
    Excelforum is Completely Awesome! True learning with Live Examples & Best Techniques!!

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Automatically Protect a single cell

    Please Login or Register  to view this content.

    Version 2:

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: Automatically Protect a single cell

    Thanks for replying!

    I've check ur both the codes; version2 is better, but still I'm getting an error msg — Run-time error '1004': Unable to set the locked property of the Range class.


    When the cell already has protected, then I don't know sometimes how i'm getting error msg on selecting those cell/cells? Kindly check out the attached.

    . . .and how can I apply UserInterFaceOnly:=True for allowing the formatting on the protected cell/cells?
    Attached Files Attached Files
    Last edited by SunOffice; 03-14-2011 at 12:30 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Automatically Protect a single cell

    'Version 3
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: Automatically Protect a single cell

    Version 3 is awesome man! Working perfectly!!

    I am able to do formatting on the blank cell/cells, not after the protection.

    my 1st question: 1) How can I add UserInterFaceOnly:=True in the protection, so they are just allow to format the cell/ cells, but can't delete anything?

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Automatically Protect a single cell

    To "Tweak" the protection setttings just start recording a macro , then protect a sheet with the settings that you want. Once you stop recording you can cut/paste the appropriate info out of recorded macro into your "protection" macro...


    EXAMPLE:

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: Automatically Protect a single cell

    Awesome man!! Superb Trick!!

    Thanks Thanks Thanks for your amazing help and tricks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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