*Hi,*
*Please go through the below article. It will help you on how to unprotect
only few cells using three different ways.
*
*Let me know if you find any issues.*
*Regards,*
*Excel King*
The easiest and most common way of barring people from playing with your
formulas is to protect your worksheet. However, protecting your worksheet
doesn't just prevent users from tampering with your formulas, it also stops
users from entering anything at all. Sometimes you do not want to go this
far.
By default, all cells on a worksheet are locked; however, this has no effect
unless worksheet protection has been applied. Here is a very easy way to
apply worksheet protection so that only formula cells are locked and
protected.
Select all cells on your worksheet, either by pressing Ctrl-A or by clicking
the gray square at the intersecting point of column A and row 1. Then select
Format → Cells → Protection and uncheck the Locked checkbox to remove the
tick. Click OK.
Now select any single cell, select Edit → Go To... (Ctrl-G or F5), and click
Special. You'll see a dialog box such as that in .
Figure 1. The Go To Special dialog
Select Formulas from the Go To Special dialog and, if needed, limit the
formulas to the subtypes underneath. Click OK. With only the formula cells
selected, select Format → Cells → Protection and check the Locked checkbox
to insert a tick. Select OK. Now select Tools → Protection → Protect
Worksheet to protect your worksheet and apply a password if required.
The preceding method certainly saves a lot of time and eliminates possible
errors locating formulas so that you can protect them. Unfortunately, it can
also prevent users from using certain features, such as sorting, formatting
changes, aligning text, and many others you might not be concerned with,
even when in an unlocked cell. You can overcome this problem in two ways.
The first approach doesn't use worksheet protection at all, and uses data
validation instead.
WARNING
Data validation is far from bulletproof when it comes to preventing users
from entering nonvalidated data into cells. Users can still paste into a
validated cell any data they want and, in doing so, remove the validation
from that cell unless the copied cell also contains data validation, in
which case this validation would override the original validation.
To see what we mean, select any single cell, select Edit → Go To... (Ctrl-G
or F5), and click Special. Now select Formulas from the Go To Special dialog
and, if needed, limit the formulas to the subtypes underneath. Click OK.
With only the Formula cells selected, select the Data → Validation →
Settings page tab, select Custom from the Allow: box, and in the Formula
box, enter ="", as shown in . Click OK.
Figure 2. Validation formulas
This method will prevent a user from accidentally overtyping into any
formula cells — although, as stressed in the earlier warning, it is not a
fully secure method and should be used only for accidental overtyping, etc.
However, the big advantage to using this method is that all of Excel's
features are still usable on the worksheet.
The last method also will enable you to use all of Excel's features, but
only when you are in a cell that is not locked. To start, ensure that only
the cells you want protected are locked and that all other cells are
unlocked. Right-click the Sheet Name tab, select View Code from the pop-up
menu, and enter the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect Password:="Secret"
Else
Me.Unprotect Password:="Secret"
End If
End Sub
If no password is used, omit Password:="Secret". If a password is used,
change the word Secret to your password. Press Alt/&command;-Q or click the
X in the top righthand corner to get back to Excel and save your workbook.
Now, each time you select a cell that is locked, your worksheet will
automatically protect itself. The moment you select any cell that is not
locked, your worksheet will unprotect itself.
WARNING
This hack doesn't work perfectly, though it usually works well enough. The
keyword used in the code, Target, will refer only to the cell that is active
at the time of selection. For this reason, it is important to note that if a
user selects a range of cells (with the active cell being an unlocked cell),
it is possible for him to delete the entire selection because the target
cell is unlocked and, therefore, the worksheet automatically will unprotect
itself.
On Sun, Dec 20, 2009 at 9:00 PM, Vinod N <nvino...@gmail.com> wrote:
> Hi Excel Gurus,
>
> I have an shared excel file saved in the shared folder...
>
> I want to allow users to use from Range A21:K3020. I tried protecting the
> worksheet but there are many limitation. Is there other way of locking other
> that protect worksheet...
>
> Please help...
>
> --
> Thanks and Regards
>
> Vinod N
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> http://www.excelitems.com
> 2. Excel tutorials at http://www.excel-macros.blogspot.com
> 3. Learn VBA Macros at http://www.vbamacros.blogspot.com
> 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>
> To post to this group, send email to excel-macros@googlegroups.com
> If you find any spam message in the group, please send an email to:
> Ayush Jain @ jainayus...@gmail.com or
> Ashish Jain @ 26may.1...@gmail.com
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,500 subscribers worldwide and receive many nice notes about
> the learning and support from the group. Our goal is to have 10,000
> subscribers by the end of 2009. Let friends and co-workers know they can
> subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
