protecting certain cells

[The Data Rat]

Newbie to this group...

We keep all of our company info on a complicated but nicely formatted little
excell spread sheet. Is there anyway to prevent/protect the formatted cells
from someone deleting/typing in them?

In other words, if cell C3 is the sum of cell C1 and C2, how can I prevent
anyone from changing C3, but allow them to enter info in cells C1 and C2?
Needless to say, losing the formatting defeats the whole purpose and I have
spent hours going through the spread sheet trying to find where the
formatting was deleted/changed.

I am using xp on one computer and 98 on another.

Thanks!

 

[Jerry W. Lewis]

Select cells C1 and C2, the Format|Cells|Protection and uncheck
"Locked", then Tools|Protection|Protect Sheet.

Jerry

 

[Jim Carlock]

Going along with this question...

Is there a way to programmatically lock the cells without
using the protect sheet function?

 

[Dave Peterson]

How dangerous do you want to live?

You could use an event macro that checks the range. If it's in a "locked" cell,
then undo the change.

For example, I selected my range of cells that I don't want changed. (I clicked
on the first area, then ctrl-clicked on the subsequent areas until I was done.)

I gave that range a nice range name of "myProtectedRange" (insert|name|Define)

Then I could use this event macro. (right click on the worksheet tab that has
this behavior and select view code.

Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myProtectedRange As Range
Set myProtectedRange = Me.Range("myprotectedrange")

On Error GoTo errHandler:

If Intersect(Target, myProtectedRange) Is Nothing Then
Exit Sub
Else
With Application
.EnableEvents = False
.Undo
End With

If Target.Cells.Count > 1 Then
MsgBox "Please change one cell at a time"
Else
MsgBox "Cannot change this cell"
End If

End If

errHandler:
Application.EnableEvents = True

End Sub

But there are lots of things that could make this useless. The user opens the
workbook with macros disabled. They do something that turns off the event
handler.

I don't think I'd use this for most things. Either I'd learn to live with the
restrictions that locked cells on protected sheets have or supply another way
(macro & toolbar??) that give the user the capability that I want them to have.