+ Reply to Thread
Results 1 to 12 of 12

Protect Data Validation

  1. #1
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Protect Data Validation

    Hi All,

    In the shared workbook I wanted to protect format and contents of cell E4 where drop down list is given (Validation). all cells apart from E4 are locked so editing is not possible and my all formulas are safe within the worksheet.

    Generally validation list not allowing to put any other contents but if I am copy pasting any other cell contents, it is allowing to paste.

    If I am locking E4 then drop down is not working, means I can not select other values of drop down and if it is not locked then format of the cell can be change by pasting.

    Is there any way to protect format of E4 while cell is unlocked along with only given list values.

    Please find attached shared workbook for reference which is protected with blank password.

    Thanks
    Ganesh.
    Attached Files Attached Files

  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: Protect Data Validation

    Good evening Ganesh7299

    This problem is almost as old as Excel itself, and Microsoft either CBA or don't see the need to fix it.

    However, John Walkenbach has posted an extremely clever way to combat the problem here. Simplicity itself, and extremely clever.

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: Protect Data Validation

    Hi DominicB,

    Thanks for your reply,
    I tried the below given code but it is not working, I have changed worksheet name as module name and I have also removed sharing of workbook but still things are not happening according the code.

    Do I need to change module name? (How?)

    Please Login or Register  to view this content.
    Thanks,
    Ganesh

  4. #4
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: Protect Data Validation

    Hi Ganesh7299

    Do not change the name of the routine. This is event procedure code (ie it is fired by an event - in the case every time a worksheet changes : read the title of the sub and you'll get the gist ...).

    It also doesn't go in a normal module. Right click your worksheet and select View Code. Put the code in the pane that opens.

    Naturally, you'll have to unshare the workbook while you get this sorted. Don't bother resharing it till you're happy it's working properly.

    HTH

    DominicB

  5. #5
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: Protect Data Validation

    Hi again,

    I have tried, but not working and excel is getting hanged...

    could you please help me out, find attached file with the code....

    Thanks
    Ganesh.
    Attached Files Attached Files

  6. #6
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: Protect Data Validation

    Hi Ganesh7299

    OK. Have a look at the attached workbook.
    You didn't have a named range called ValidationRange which the routine depends on, so I have set the up.
    When I was having a little test, the routine seemed to fall over when drag and drop was being used : the easiest way to get around that was to disable drag and drop - the routine to do this is in the ThisWorkbook module. The file should now work fine if someone tries to copy and paste a cell over the DV.

    HTH

    DominicB
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: Protect Data Validation

    Hi Dominic.

    I just checked attached workbook, but still it is not working.

    Just inform you that I have a named range ValidationRange also ("test").
    And I made changes in the code, I thought that I need to mention name of my valildation range in the code.

    Please help.........

    Thanks,
    Ganesh.

  8. #8
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: Protect Data Validation

    Hi Ganesh7299
    Quote Originally Posted by Ganesh7299 View Post
    I just checked attached workbook, but still it is not working.
    I've just checked the file and it seems to be working OK. Can't drag and drop and can't paste over the DV.
    You are allowing the macros to run aren't you? It sounds as though you aren't.

    HTH

    DominicB

  9. #9
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: Protect Data Validation

    Okay, I guess i am going wrong somewhere,

    Here in the attached file (test) I have named range called "test" (A1:A10) which has validation. Code is also in place.

    If I am coping cell B1 from the same sheet and try to pasting in validation range macro is working and giving me msgbox.

    But problem is accruing when I am coping cell from any other sheet and pasting in the validation range macro is not working.

    e.i. If cell A1 copied from Sheet2 and pasted on Sheet1 range A2 (Validation Range) macro is not working.

    Please find attached file.......

    Code:
    Please Login or Register  to view this content.
    Thanks,
    Ganesh.
    Attached Files Attached Files

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Protect Data Validation

    But you'll notice that the validation is unchanged ...
    Entia non sunt multiplicanda sine necessitate

  11. #11
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: Protect Data Validation

    Hi,

    Validation is unchanged but in the validation range other values can be pasted, if copied from other sheet.

    Is there any way to protect cell/range in a such way that other user can only select the contents from given list in shared workbook.

    Thanks,
    Ganesh

  12. #12
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: Protect Data Validation

    Is there any alternate for the same??????????

+ Reply to 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