Protect cells without protecting the entire workbook.

  • Thread starter Thread starter YY san.
  • Start date Start date
Y

YY san.

Dear experts,
Is there any VB script of function in Excel to protect certain cells, but no
need to protect the entire worksheet?

Thanks,
 
Dear experts,
Is there any VB script of function in Excel to protect certain cells, butno
need to protect the entire worksheet?

Thanks,
Click to expand...

Unlock all the cells in the worksheet, then lock the ones you want
protected, then protect the sheet, only the Locked cells are protected.
 
No

Cell protection requires Worksheet Protection be enabled.

Provide a description of what you would like to do.

The usual method is to unprotect, do the deed then re-protect.


Gord Dibben MS Excel MVP
 
You might be able to make use of this idea. Right click tab for the
worksheet that has the cells you want to "protect" and select View Code from
the popup menu that appears, then copy/paste the following code into the
code window that appeared (see my additional comments after the code)...

'*************** START OF CODE ***************
Dim OldValue As Variant
Private Const ProtectedAddresses As String = "A1,B2,C3,D4"

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
MsgBox "The value in this cell cannot be changed!"
Target.Value = OldValue
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then
OldValue = Target.Value
End If
End Sub
'*************** END OF CODE ***************

Change my example cell address (A1,B2,C3,D4) to the cell addresses of the
cells you want to "protect" in the Private Const statement at the beginning
of the code. Now, go back to the worksheet and try to change the values in
those cells.
 
LOL... we all do Gord, not just you. I can't even begin to tell you how much
I have learned from you, as well as the others here, since I started
volunteering in the Excel newsgroups. The newsgroups are an amazing resource
for all... from novice users right up to the most experienced users.

As for the code... using "global" variables to set up a cooperative exchange
of information between event procedures... I got quite good at implementing
this type of coding back when I volunteered in the compiled VB newsgroups. I
ended up developing several solutions to "it can't be done" type problems
back then using this technique... the technique can be quite powerful (when
the situation is right for it).
 
Hi Rick,
Thanks for your help, it is exactly what I wanted. By the way, can the cell
ID in the code change to a range instead of individual cell ID? I have like
A1 ~ A50. Thanks,

To the rest of the folks,
thanks for your responses too! Totally agreed, we are all still learning. I
have got great tips and solution after I hooked onto this forum!
 
Yes... use a colon to join the top-left cell to the bottom-right cell. So,
for your example...

Private Const ProtectedAddresses As String = "A1:A50"

And, of course, these can be combined with other single cells and/or ranges.
For example...

Private Const ProtectedAddresses As String = "A1:A50,B2,C3:F6,G4"

Any valid range designations (single cells or multi-cell ranges) can be
combined using the comma to separate them.
 
Something else you may want to consider doing with my code. As posted, my
code will pop up the "The value in this cell cannot be changed!" message,
but leave the active cell at the location it moved to when the user hit
Enter or mouse clicked. Better (I think) would be to re-activate the cell
the user attempted to change so the message that popped up references what
the user is looking at when he/she dismisses the MessageBox. To accomplish
this, just add this line of code...

Target.Select

immediately *after* this line of code...

Target.Value = OldValue
 
Hi Rick,

I also need to have this code to lock some cells.

But do you know where on the code sheet to insert that code?

Thanks
 
".....Right click tab for the worksheet that has the cells you want
to "protect" and
select View Code from the popup menu that appears, then copy/paste
the following code into the code window...."
 
This is amazingly useful!

I have a further question. If you use this code it protects your cells and makes sure the data remains untouched just as described.

However if your cells have references to other cells (or formulas) in them, then when you type in anything and you get the pop up message box that says "The value in this cell cannot be changed!" and then push 'enter" to get rid of the box afterwards the cells referencing has been lost.

Click on the cell that had your reference in it, this has gone and is replaced with whatever was previously written in it. So it basically seems to "copy and paste values" and you lose your reference.

So for example I had a reference to a date in a cell "=COVER!G2". The text of this said "27/10/10". When i tried typing over the cell I get the message box and press enter. And then when I go back to the cell the reference is gone and it says "27/10/10"

I have multiple cells with references / formulas within a protected area. Does anybody know how to get around this to maintain references / formulas?

Thanks!
 
Back
Top