Additional Help with Cell Protection

[Tanachit Kasintorn]

I need to protect a few cells in my worksheet(i.e., A1
and B3). As for other cells in the same worksheet, I want
the users to be able to change the content.
To put it simply, I don't want the users to change the
contents of a few cells in the worksheet while still
having access to other cells in the same worksheet. Is
there a way to protect those few cells without protecting
the whole worksheet? Help is much appreciated.

[Dave Peterson]

If you're using xl2k or lower, you can lock/unlock cells. But then you have to
protect the sheet.

Once the sheet is protected, the unlocked cells can have the contents changed.
But the locked cells won't accept user changes.

If you're using xl2002, there are a few more options for protection that allow
users to do other things.

========
Another option might be to have a worksheet event that puts back the
formula/value if it's ever changed.

Then you wouldn't care if they changed it.

Kind of like this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("a1,b3")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Select Case LCase(Target.Address)
Case Is = "$a$1"
Target.Formula = "=sum(a2:a99)"
MsgBox "please don't change this!"
Case Is = "$b$3"
Target.Value = 99
MsgBox "please don't change this!"
End Select
Application.EnableEvents = True
End Sub

--

Dave Peterson
ec3...@msn.com

[David McRitchie]

All cells are protected by default, but the protection is not
effective until the sheet protection is turned on.

Ctrl+A select all cells
Format, cells, protection, uncheck both (all) boxes
ok
Select the cells to be protected ( A1 and B3)
Format, cells, protection, [x] locked

Tools, protection, protect sheet (password optional)
[x] contents, [x] objects, [x] scenarios

-- To make accessible again: Tool, Protection, unprotect sheet
(password required if protected with password)

Protection, Worksheet
http://www.mvps.org/dmcritchie/excel/protection.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tanachit Kasintorn" <tkas...@hotmail.com> wrote in message news:55d501c1b7b4$f11eac00$b1e62ecf@tkmsftngxa04...

[David McRitchie]

Nice one.

"Dave Peterson" <ec3...@msn.com> wrote in message news:3C6FB5AB...@msn.com...

[Tanachit Kasintorn]

Thank you very much, Dave. Your help is much
appreciated.
I was wondering if you know how to make it so that certain
cells cannot even be selected using a mouse click or arrow
keys. I saw it done in one of the invoice template
provided with Excel. When I tried selecting (with a mouse
click) a particular cell, which is protected, it won't
even let me select it. The same goes for arrow keys.
When I move through cells using arrow keys, it just skip
the protected cells when I move through them.

>.
>

[Tom Ogilvy]

You can set the enableSelection property of the worksheet to Unlocked Cells.

Unfortunately, this setting is not persistent and must be set through code
each time the worksheet is opened. This can be done in the Workbook_Open
event.

If you don't know how to access this event, post back.

Regards,
Tom Ogilvy

Tanachit Kasintorn <tkas...@hotmail.com> wrote in message

news:484901c1b7bc$e14e6330$a5e62ecf@tkmsftngxa07...

[Tanachit Kasintorn]

Hi Tom:
I only know this:
Sub DisableLockedCells()

With ActiveSheet
.Protect
.EnableSelection = xlUnlockedCells
End With

End Sub

I tried these codes but they protects the whole sheet.
What I need is something to protect only few cells while
leaving other cells to be available for changes/selecting.
As for EnableSelection property of worksheet, will it
protect the whole worksheet the same way the above codes
do or will it allow for selective cell protection? As for
EnableSelection property, I have no clue how to do it.

There are two issues here. First, I want to control how
users change the contents of certain cells. Second, I
want to disallow users from selecting certain cells (thus
avoid content change). As for the first issue, I think I
know how to do it. The second issue is however more
problematic. In addition to protecting the cell content,
I need something to prevent those protected cells to be
selected(i.e., something that will prevent cell selection
through a mouse click or an arrow key move).

Thanks

>.
>

[Paul]

Hit control A to select the whole page, go to format, cells, protection,
uncheck locked, ok, select the cells you want to lock then go to format,
cells, protection and check locked, ok, go to tools, protection, protect
sheet. Paul

"Tanachit Kasintorn" <tkas...@hotmail.com> wrote in message

news:55d501c1b7b4$f11eac00$b1e62ecf@tkmsftngxa04...

[Tom Ogilvy]

You code is using the EnableSelection property.

Each cell on your sheet is either locked or unlocked. This has no effect
until you protect the sheet - when you do, locked cells can not be edited
and unlocked cells can be edited. Assume you want A1, B2, C3 to be
protected.

Sub DisableLockedCells()

With ActiveSheet
Set rng = Range("A1,B2,C3")
.Cells.Locked = False
rng.Locked = True

.Protect
.EnableSelection = xlUnlockedCells
End With

End Sub

Put the above code in a general module - Alt+F11, puts you in the VBE.
Insert=>Module. Will guve you a general module.

In the project explorer on the left, you will see a ThisWorkbook entry under
your workbook. Double click on this an you will get the module for Workbook
Level events. In the left dropdown at the top select
Workbook
in the Right,
Open
This will put in the Workbook_Open Event.

Private Sub Workbook_Open()

End Sub

Put code like this in

Private Sub Workbook_Open()
Thisworkbook.Worksheets(1).Activate
' no call you sub
DisableLockedCells
End Sub

Regards,
Tom Ogilvy

Tanachit Kasintorn <tkas...@hotmail.com> wrote in message

news:485c01c1b7c3$2921c7f0$a5e62ecf@tkmsftngxa07...

[Tanachit Kasintorn]

Dear Tom:
You are a genius!
Tanachit Kasintorn

>> >> even let me Í{ wÀ ?¾0à@,( ¡
>°ì: select it. The same goes for arrow keys.