An alternative to sheet protection

[K. Georgiadis]

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"

[gsouza]

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

[K. Georgiadis]

I will, thanks!

[andy_uk]

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

[K. Georgiadis]

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!

[gsouza]

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.

[K. Georgiadis]

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.

[andy_uk]

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

[K. Georgiadis]

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!

:stars:

[gsouza]

Don't surrender, its all good stuff

[K. Georgiadis]

I'll put the white flag away if you or anybody else have other suggestions:dunno

[Aaron Blood]

Out of my own curiosity... Why is it you'd prefer not to use sheet protection?

[K. Georgiadis]

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.

[gsouza]

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

[K. Georgiadis]

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")

??

[gsouza]

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.

[Zack Barresse]

A recent example .. http://www.mrexcel.com/board2/viewtopic.php?t=138581&highlight=

[K. Georgiadis]

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

[Zack Barresse]

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. ;)

[K. Georgiadis]

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