Solved

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

Posted on 2000-03-16
30
326 Views
Last Modified: 2012-05-04
In Lotus 123 you used to be able to protect range without affecting other parts of a sheet or features such as format, or insert row.  It was easy to select a range and protect it, or unprotect it.  Is there any way that the same thing can be done in Excel 97 (even if it means writing the VBA to do it)?
0
Comment
Question by:maryelizabeth
  • 11
  • 8
  • 5
  • +2
30 Comments
 
LVL 22

Expert Comment

by:ture
ID: 2626968
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
0
 
LVL 22

Expert Comment

by:ture
ID: 2627137
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
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2627324
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
0
 
LVL 9

Expert Comment

by:antrat
ID: 2627476
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
0
 
LVL 22

Expert Comment

by:ture
ID: 2627492
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
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2627507
Thanks Ture, nice compliment :-)
0
 
LVL 22

Expert Comment

by:ture
ID: 2627526
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
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2627573
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
0
 
LVL 22

Expert Comment

by:ture
ID: 2627636
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
0
 
LVL 9

Expert Comment

by:antrat
ID: 2627639
>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

0
 
LVL 13

Expert Comment

by:cri
ID: 2629717
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.
0
 
LVL 9

Expert Comment

by:antrat
ID: 2630131
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
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2630390
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
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2630392
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.
0
 

Author Comment

by:maryelizabeth
ID: 2631459
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

by:calacuccia
ID: 2631554
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
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2631570
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
0
 
LVL 9

Expert Comment

by:antrat
ID: 2632980
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

0
 

Author Comment

by:maryelizabeth
ID: 2633055
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

0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2633189
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

0
 
LVL 9

Expert Comment

by:antrat
ID: 2633261
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
0
 
LVL 9

Expert Comment

by:antrat
ID: 2633277
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
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2633492
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

0
 

Author Comment

by:maryelizabeth
ID: 2633743
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
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2634153
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



0
 
LVL 9

Accepted Solution

by:
antrat earned 200 total points
ID: 2634912
Hi maryelizabeth

Yes, It will only work if you have both procedures in the worksheet Event module.

I have modified my code so that the user is unable to delete. if any cells in the selection has a font color of DarkBlue(Data Validation)then the deletion is UnDone.

To implement it:

1:Select the cells you want partial protection on then go to Format>cells>Font>Color and select DarkBlue. Then with the range still selected go to Data>Validation and Validate to Custom ="", or any preset Validation.

NB:It is a good idea to make Validated cells stand out in some way so users know which cells are Validated.


2:Right click on the SheetName tab and paste in the entire code below over any pre-exsiting code.


Option Explicit
'This Dim statement must stay here
Dim MyData As Variant
Dim mycell As Range, flag As Integer

Option Explicit
'This Dim statement must stay here
Dim MyData As Variant
Dim mycell As Range, flag As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
flag = 1
For Each mycell In Selection
   If mycell.Font.ColorIndex = 16 Then 'DarkBlue
      flag = flag + 1
    End If
Next
If flag > 1 Then
   Application.CutCopyMode = False
 End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If flag > 1 Then
Application.EnableEvents = False
 Application.Undo
Application.EnableEvents = True
End If
End Sub



It will stop:

1:pasting into the Validated area(DarkBlue Font).

2:Deleting any cell content if any part of the selection contains a Validated cell(DarkBlue Font).

3:Data entry as set by Validation.

The users will be able to copy FROM the Validated cells, but this is the default for Excel and can be overcome by using PasteSpecial. I would suggest not altering the defaults of Excel too much as this can confuse users.


Calacuccia Thanks for responding.
I hate to be negative but,
Your code is still preventing deletion from the entire worksheet.


antrat
0
 

Author Comment

by:maryelizabeth
ID: 2635467
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
0
 

Author Comment

by:maryelizabeth
ID: 2635476
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
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2635529
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
0
 
LVL 9

Expert Comment

by:antrat
ID: 2657804
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How many times recently have you prepared a presentation or emailed a document to a client and you have found that they have older versions of MS Office and they can not open the file you have prepared.  Although most visitors to this site are exper…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now