Consulting

Results 1 to 20 of 20

Thread: An alternative to sheet protection

  1. #1

    An alternative to sheet protection

    I was taught a neat technique whereby, by using Data Validation, I can prevent any data entry in selected cells, without the use of sheet protection.

    Out of curiosity, is there a way to accomplish the same thing with VBA so that data entry would be prohibited in specified cells and a customized message would appear if the user attempted to make data entries; something like "data inputs must be made in Worksheet XYZ"

  2. #2
    VBAX Contributor
    Joined
    Dec 2004
    Posts
    122
    Location
    Try this, place this in a worksheet module

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Application.Intersect(Target, Range("A1:Z50")) Is Nothing Then
    Range("a1").Select
    'MsgBox "Do not edit this cell."
    End If
    end sub

  3. #3
    I will, thanks!

  4. #4
    VBAX Regular
    Joined
    Jul 2004
    Location
    Cambridge, England
    Posts
    23
    Location
    by using Data Validation, I can prevent any data entry
    in selected cells, without the use of sheet protection
    Not so. Data Validation is entirely unsecure, you can copy & paste over it from another sheet or another workbook.

    gsouza's method works, albeit rather aggressively ; it prevents cells from being selected, rather than just changed.

    In the past I've used a "two-handed" approach to this problem:

    (i) Worksheet_SelectionChange code to store the value of the selection *somewhere* if it's in the restricted range

    (ii) Worksheet_Change code to immediately *re*store the value of a changed cell in the restricted range.

    Thus, (i) =

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        If Not Application.Intersect(Target, Range("A1:H10")) Is Nothing Then
            Range("I1") = Target.Value
        End If
    End Sub
    and (ii) =

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Application.Intersect(Target, Range("A1:H10")) Is Nothing Then
            Application.EnableEvents = False
            Target.Value = Range("I1")
            Application.EnableEvents = True
            MsgBox "Changes to cell " & Target.Address & " aren't allowed."
        End If
    End Sub
    However, like most of my methods it's uber-sloppy. I'm sure regulars here will come up with something far more robust & efficient.

    HTH,
    Andy

  5. #5
    Thanks, Andy. The people who that will be using my workbook probably are not even aware of Data Validation but I'm always interest in more robust and secure methods. I'll give these a try!

  6. #6
    VBAX Contributor
    Joined
    Dec 2004
    Posts
    122
    Location
    Although I like it,the only problem is that if you highlight all the cells in the range at the same time you can delete the data in Andy's method. Well not delete it but change it all to the same value.

  7. #7
    There is another problem (which may eventually steer me to using Excel's standard sheet protection!):

    my "restricted range" is a calculated range, i.e. it receives its values from another worksheet. My objective is to let the user know that he should be making his data inputs in a different worksheet. Not only does Andy's method prevent data entry in the restricted cells, it also blocks the cells from receiving updated data inputs from the "source" worksheet.

    Maybe what I need is worksheet protection but with a customized message box, telling the user specifically where the data inputs must be made.

  8. #8
    VBAX Regular
    Joined
    Jul 2004
    Location
    Cambridge, England
    Posts
    23
    Location
    Quote Originally Posted by K. Georgiadis
    Not only does Andy's method ..., it also blocks the cells from receiving updated data inputs from the "source" ws
    To be honest, that's not the case for me. Plus, what you're after now sounds more like gsouza's original suggestion.

    You can bump the user back to the last viewed sheet conditionally, eg:

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If ActiveSheet.Name = "Sheet1" Then
    Worksheets(Range("Home")).Select
    MsgBox "Data inputs must be made on sheet blah."
    Else
    Worksheets("Sheet3").Range("A1") = ActiveSheet.Index
    End If
    End Sub
    , where a cell on Sheet3 is the named range "Home". NB: this goes in the workbook module ; & if anyone has ideas on how to do this cleanly, I'd be very interested.

    You do know that sheet protection only applies to those cells that are also locked (Format -- Cells -- Protection), yes?

    HTH,
    Andy

  9. #9
    Andy, in my test I modified your two sets of code to make B2:G2 the restricted range. I have since determined that the cells will in fact accept values from the other worksheet except for cell B2. I am not sure why that is, unless I managed to butcher your code in the translation! I'll give gsouza's original suggestion a try also. Yes, I use sheet protection extensively and I am familiar with locked vs. unlocked cells

    I tried gsouza's code but that is not exactly what I'm looking for either. You know what, guys? Of course I'm curious if this can actually work but I also think that I can live with good ole fashioned sheet protection. I surrender!


  10. #10
    VBAX Contributor
    Joined
    Dec 2004
    Posts
    122
    Location
    Don't surrender, its all good stuff

  11. #11
    I'll put the white flag away if you or anybody else have other suggestions

  12. #12
    VBAX Contributor Aaron Blood's Avatar
    Joined
    Sep 2004
    Location
    Palm Beach, Florida, USA
    Posts
    130
    Location
    Out of my own curiosity... Why is it you'd prefer not to use sheet protection?

  13. #13
    Hi Aaron, I do use sheet protection extensively but in this instance I was looking for a customized message; instead of the standard "the cell or chart you are trying to change is protected and therefore read-only," something like "data entry for these cells must be made in Sheet xyz."

    Since I don't believe I can mess with Excel's standard message, I was looking for an alternative method to block data entry. However, it is not a huge deal and I can work around it thus: apply data validation that allows any number entry, apply my customized message as a data validation input message, apply sheet protection.

  14. #14
    VBAX Contributor
    Joined
    Dec 2004
    Posts
    122
    Location
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Application.Intersect(Target, Range("B2:G2 ")) Is Nothing Then
    Range("a1").Select
    Worksheets("sheet2").Select
    MsgBox "This is the sheet to edit."
    End If
    End Sub

  15. #15
    Gsouza, let me make sure I understand how this works: if Sheet2 is the sheet to edit, and if the user attempts to make entries in Sheet1, he receives the message and is taken to Sheet2?

    Hey, I tried this and it works, giving me an alternative method. Thanks!!!

    gsouza: just to be certain:

    1) On line 2, I am assuming that the extra space between G2 and the closing quotation marks in Range("B2:G2 ") does not have any significance
    2) if I wanted to restrict non-contiguous ranges in the same sheet, could I write it:

    Range("B2:G2", "B13:G13", "B18:G13")
    ??

  16. #16
    VBAX Contributor
    Joined
    Dec 2004
    Posts
    122
    Location
    Yes the space was a type error Range("B2:G2 ") it does not have any significance. I am not sure about the non-contiguous ranges in the same sheet, so you should just mess around with it. Let me know if it works?
    I am still not sure if this is what you want to do.

  17. #17

  18. #18
    yes, my assumption about the syntax regarding non-contiguous ranges was correct; just type as many non-contiguous range addresses as you like, between quotation marks, and separated with commas.

    In terms of what I am trying to accomplish, your method gives me yet another option even though it does have some drawbacks: for example, not only am I not able to edit the restricted cells, I cannot even select them to change formatting, background color, anything at all.

    As I responded to Aaron Blood in this thread, my baseline solution is to use a combination of sheet protection and data validation messages

  19. #19
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    The problem with using VBA for a protection such as this (and not using the SelectionChange event) is that it is so easy to bypass or make the code fail. You would have to keep an entire sheet just for the past values and then have some procedures run to copy the values with certain changes, and then if a "locked" cell has been edited, copy the right material back, etc, etc. It's sort of a pain, imho; thus easier to just utilize the native protection and keep VBA out of it altogether.

    You see, it's easier to bypass a VBA solution because all you need to do is choose Disable Macros wehn opening the workbook.

  20. #20
    Thanks, Zack. That's why I will probably use the "native" Excel solution that I described to Aaron Blood

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •