Protect a Range in Excel without disabling other features (eg format)

maryelizabeth,

I have a solution that will stop users from typing data into a range of cells, but they will still be able to copy-and-paste data there, so it is not a really safe protection...

Is this good enough for you?

Ture Magnusson
Karlstad, Sweden

maryelizabeth,

Here's a better solution... If you need more detailed explanations for the steps below, please ask back. I assume here that it is Sheet1 that you want to protect.

1. Copy Sheet1 and name the copy 'Orig'.
2. Protect and hide the 'Orig' sheet.
3. Name the range on Sheet1 that you want to protect. The name should be "LockedRange".
4. Right-Click the Sheet1 tab and select 'View Code'. Enter this VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("LockedRange")) Is Nothing Then
    Application.EnableEvents = False
    Sheets("Orig").Range(Target.Address).Copy Destination:=Target
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  End If
End Sub

5. Go back to Excel and try entering values in the locked range on Sheet1. The procedure will automatically copy the changed range back from the 'Orig' sheet.

Ture Magnusson
Karlstad, Sweden

Hi MaryElizabeth,

Here's another way. If you put this macro in the worksheet_change sub (right-click on sheet tab, select 'View Code', put macro beneath in there, you simply will have to set the cells property locked to off to protect them.
Quie funny, isn't it. To set this property off, select any range, right-click and select 'Format Cells', go to tab 'Protection' and uncheck box 'Locked'.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Flag As Boolean
Flag = False
For Each c In Target.Cells
If c.Locked = False Then
Flag = True
End If
Next c
If Flag = True Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub

Have Fun

Calacuccia

Hi maryelizabeth

There is not really any need for VBA to achieve what you want, as Excel97 has a very handy feature called "Data Validation" to use this highlight the cells or sheets you wish to protect and then go to Data>Validation ,you can use one of the preset validations or you can make your own by using "Custom" if you wish to use custom to prevent any data being entered, but still allow formating and column and row insertion then simply type ="" in the formula box.

The other good thing about this is you can add your own message and level of protection ie: Stop= No entry, Warning= Entry after warning and Information= Entry with message

If you have any other queries just yell

antrat

Calacuccia,
I REALLY like your approach! Very nice idea!

antrat,
Your suggestion is what I had in mind at first when I saw this question. However, it has a drawback - it will only prevent TYPING text into the cells. If will not stop anyone from copying/pasting etc.

/Ture

Thanks Ture, nice compliment :-)

Calcuccia,

Here is a modification of your excellent idea. This code protects a range if it's 'Locked' setting is checked.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim c As Range
  For Each c In Target.Cells
    If c.Locked Then
      Application.EnableEvents = False
      Application.Undo
      Application.EnableEvents = True
      Exit Sub
    End If
  Next c
End Sub

Note: None of our suggestions prevent the user from changing the cell's formatting. We only stop changing the cell contents.

/Ture

Ture,

that's exactly what I first tried. Disadvantage, won't work correctly when you paste a range of cells. I don't see exactly why, but it's related to the undo repeated several times, I guess.

I choose to use c.Locked = False, because this is set as True by default, so it's more logical to only protect cells you wish, instead of protecing them all.

Indeed, nothing is preventing the user to change the settings by using Format/Cells/Protection..

Calacuccia

Calcuccia,

Problem! I found that your approach (which I liked so much) will not work properly if non-protected cells are copied into the protected range. As soon as they are copied into the range, the Target.Locked of the destination range will be overwritten.
You see what I mean?

We could use a combination of our approaches... checking if the target is within a named range and perform an Undo if that is the case...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Not Intersect(Target, Range("Locked")) Is Nothing Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  End If
End Sub

/Ture

>not stop anyone from copying/pasting etc.

Very true,just seems easier. I'm not sure maryelizabeth want's to disable copying and pasting. Besides I think data validation is overlooked a lot :)

But if's It's a good VBA procedure maryelizabeth wants well I don't think you can go wrong with a CalacucciaTure
function.

antrat

Listening...

Quattro Pro 4.0 for DOS (8 years ago...) allowed to select the protection level for content and formatting.

Excel 97 8 (aka version 8) still does not have it, the only improvement is that now the outline feature will work if you opt to.

Hi maryelizabeth,

If you use the "Data Validation" method then you could also prevent users from
pasting data into the cells by applying these few lines of code to the
worksheet(s). To put them in simply right click on the sheet(s) name tab you
apply the Data Validation to and place the code below in.
What it does is clear the Clipboard(area Excel stores copied data) if they
select any of the cells that have Data Validation with the "Show error alert"
option on. If you have this option off then simply change "ShowError = True " to
"ShowError = False"

As before this will still allow the user the  flexibility of being able to do
anything except type data in the cells that don't meet the criteria you set and
paste into them.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error GoTo Done
If Target.Validation.ShowError = True Then
   Application.CutCopyMode = False
    End If
Done:
End Sub


Good luck and have fun

antrat

Hi All,

 what about this one. Same functionality as my first attempt (to protect cells from modifying and allow style & format changes, set cell property 'Locked' to OFF), and this one also handles copy & pastes from the clipboard.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Application.Undo
For Each c In Target.Cells
If c.Locked = False Then
Application.EnableEvents = True
Exit Sub
End If
Next c
Application.Undo
Application.EnableEvents = True
End Sub

Calacuccia

The trick,

undo the change by default, then check if cell 'Locked' property as on/off, and repeat the undo if necessary to undo the undo, if you know what I mean.

Well, just test it and see.

I've tried antrat's Validation solution (without VB) since this was the simplest and the easiest to switch on and off (a user requirement) – it's looking good.  BUT testing shows that not only can you paste into cells protected this way, but – and this is pretty fatal from my point of view – you can also delete cell contents.  I was using the code below to move the cursor away from cells I wanted to protect.  Can this code be adapted to prevent both Paste and Delete (ie instead of moving the cursor)?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)  
 Dim rngForbidden As Range
    Set rngForbidden = Union(Range("Headings"), Range("Calc1"), _
        Range("Calc2"), Range("Calc3"), Range("Calc4"), Range("Calc5"))
    If Intersect(Target, rngForbidden) Is Nothing Then Exit Sub
    ActiveCell.Offset(0, 1).Select
End Sub

Hi MaryElizabeth,

That's exactly what we said above, of course. The problem is that you are getting quie complicated, and difficult to build macro's (I see you have 5 named ranges Calc now + the headings range). What's more, I feel it's rather complicated to overcome your problem with macro's, as the validation process for itself will do some actions, which are not always controlled.

I'd propose to consider my macro (the last one), which is quite simple, and all you have to do is to set the cells properties to Locked = False.
In the mean time, I'll try to find a solution if you want to work with data validation.

Calacuccia

Hi again,

Here is the solution if you need to use the data validation. Watch out, it will only work if you follow Antrat's specification, that is you enter "" as your custom formula. The olution is very similar to my last macro, only it will usethe Validation.Formula1 propery of the active cell.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
On Error Resume Next
Application.Undo
For Each c In Target.Cells
If c.Validation.Formula1 Is Nothing Then
Application.EnableEvents = True
Exit Sub
End If
Next c
Application.Undo
Application.EnableEvents = True
End Sub

Good Luck

Calacuccia

Hi maryelizabeth
The code below will prevent any pasting into your cells that have data validation and well also prevent anyone from deleting the cell contents.

Here is my solution in full:

Highlight the cells you wish to protect and then go to Data>Validation ,you can use one of the preset validations or you can make your own by using "Custom" if you wish to use custom to prevent any data being entered, but still allow formating and column and row insertion then simply type   =""   in the formula box.
You can add your own message and level of protection ie: Stop= No entry, Warning= Entry after warning and Information= Entry with message

Apply these lines of code to the
worksheet(s) Events. To put them in simply right click on the sheet name tab you apply the Data Validation to and place the code below in.  







Option Explicit
'This Dim statement must stay here
Dim MyData As Variant

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Value <> MyData Then
   Target.Value = MyData
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
If Target.Validation.ShowError = True Then
   MyData = Target.Value
    Application.CutCopyMode = False
 End If
End Sub




What it does is clear the Clipboard(area Excel stores copied data) if they select any of the cells that have Data Validation with the "Show error alert"
option on. If you have this option off then simply change "ShowError = True " to "ShowError = False"


The "Private Sub Worksheet_Change" is what prevents anyone from deleting the cell contents.




Any probs at all let me know

antrat

Thanks antrat.  I am working with your solution because it’s the one I most understand and it seems to address my issue most closely.  Thanks also to everybody who has contributed.

I’ve used your code, antrat,  for Worksheet_SelectionChange and that has effectively prevented pasting into the cells with Validation.

I get an interesting result with your code for Worksheet_Changed (copied from my VBE in case I’ve missed something)

Option Explicit
Dim MyData As Variant

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Value <> MyData Then
        Target.Value = MyData
    End If
End Sub

With this code operational I can still delete from cells with Validation.  Not only that for cells without Validation (ie those I need to enter data in) I cannot paste into them.  If I type data into them, it disappears.  This behaviour stops if I comment out the code from Private Sub… to End Sub.  I seem to be getting the right result but for the wrong cells!

What do you think?  Have I done something silly?

maryelizabeth

MaryElizabeth,

Don't want to repeat myself, but what you ask for is exactly done by the last macro I wrote.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
On Error Resume Next
Application.Undo
For Each c In Target.Cells
If c.Validation.Formula1 Is Nothing Then
Application.EnableEvents = True
Exit Sub
End If
Next c
Application.Undo
Application.EnableEvents = True
End Sub

I'd like you to try it, and tell if that fits your needs, we can always look further to modify Antrat's code if it fits you better afterwards.

Calacuccia

Quite right maryelizabeth

Should be like below:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Done
If Target.Validation.ShowError = True Then
If Target.Value <> MyData And Target.Value = "" Then
   Target.Value = MyData
  End If
  End If
Done:
End Sub


Calcuccia's answer will work also , however it does restrict the type of Validation that can be set. ie List, between numbers etc. But that may not effect you?

antrat

Calacuccia, It seems your code has a similar prob to my 2nd last post too, that is, it extends outside the validation area. Probaly due to the "Application.Undo"

antrat

Antrat, you're right about my last code posted. The one with the cell locked will work though.

You're last code starts to work very well, except when you copy a range outside to outside protected range, that won't work.

I propose to use the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Application.Undo
For Each c In Target.Cells
If c.Validation.Value = False Then
Application.EnableEvents = True
Exit Sub
End If
Next c
Application.Undo
Application.EnableEvents = True
End Sub

I've investigated your method Antrat, and it will be very hard to make it work if you use multiple cells to copy and paste, as the control you do is only for the upper-left cell of the selected range. Except for this problem, this modification of your code works better, and allows to copy a range outside the protected cells area:

'This Dim statement must stay here
Dim MyData As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error GoTo Done
If Target.Validation.ShowError = True Then
   MyData = Target.Value
    Application.CutCopyMode = False
 End If
Done:
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Done
If Target.Validation.ShowError = True Then
If Target.Value <> MyData And Target.Value = "" Then
   Target.Value = MyData
  End If
  End If
Done:
End Sub

But as I say, it does not handle the moment where you have a range of, say 2x2 cells, copy it, and then move to a cell just on top-left of protected area, because the Copy & Paste will not be removed from the clipboard.

I've thought about using a loop (For Each c in Target.Cells), but discovered that error handling, necessary for this to work, is very crappy when repeated error statements arrive, and almost impossible to control.

I think we're all working together to develop a great tool. And also, it seems there are two valid solution available.
The only thing still bothering me, is that when copying from inside the protected range to outside it, the range (not protected originally) in which is pasted, will be protected too, as the data validation or locked cell property will be copied too.
So somzone, copying values from the protected range into other parts of the spreadsheet, will start to protect uncontrolale parts of his worksheet. Not very Nice.

Cheers

Calacuccia

antrat - I've changed the code (copy below).  I can now delete values in unprotected part of worksheet (and copy/paste).  However, in the areas protected by the Validation I can still delete.  I think my users are more likely to loose their data by overtyping or by deleting (copy/paste is less of a problem).  What do I need to do to prevent deletion in the areas protected by validation?

Thanks

maryelizabeth

Copy of Code:
Option Explicit
Dim MyData As Variant

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo Done
If Target.Validation.ShowError = True Then
    If Target.Value <> MyData And Target.Value = "" Then
        Target.Value = MyData
    End If
   
End If
Done:
End Sub

MaryElizabth,

You'll need both the Worksheet_Change and Worksheet_SelectionChange event. Re-post:

'This Dim statement must stay here
Dim MyData As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error GoTo Done
If Target.Validation.ShowError = True Then
   MyData = Target.Value
    Application.CutCopyMode = False
 End If
Done:
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Done
If Target.Validation.ShowError = True Then
If Target.Value <> MyData And Target.Value = "" Then
   Target.Value = MyData
  End If
  End If
Done:
End Sub

I advise you to try this one though, it will also handle the copy & pasting problem (delete all other code in the module):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Application.Undo
For Each c In Target.Cells
If c.Validation.Value = False Then
Application.EnableEvents = True
Exit Sub
End If
Next c
Application.Undo
Application.EnableEvents = True
End Sub

Brilliant antrat.  It works!  Many thanks.  I appreciate that if a user pastes a block of cells the right/bottom of which co-incides with a protected area, damage will be done.  However for my purposes this is too minor an issue to worry about.  Thank you very much for providing a solution which is such a close match to my original question.

maryelizabeth

An excellent solution for my purposes (though I appreciate the protection isn't bullet-proof).  NB: I used:

MyCell.Interior.ColorIndex = 36 Then 'Yellow background

instead of the dark blue font because my protected cells are all coloured anyway.

I am very pleased with the result - lots of thanks.

maryelizabeth

Glad you found something fitting MaryElizabeth.

Antrat, Congrats
But want to add to the discussion that my last code works, both with my solution as with your slightly modified solution. At least on my version/tests. I can delete & modify every non-protected cell on the sheet, and the first version (only Worksheet Change) even prevents pasting into the protected cells. Just to let you know.

If you disagree, please show me an example of how my last code would not work, as I've put a lot of time and pleasure in this, I'd like to evaluate the outcome...

Calacuccia

Hi MaryElizabeth.
Thank you, glad to help

Calacuccia
Your last code does indeed work , I must have been trying one of your previous codes.

antrat