Lock-Protect documents

[Michael Miazga]

Excel 2002 sp2

How do you protect cells from being edited? I need to lock/protect all but
4 cells from being edited on a worksheet. I go into the 'format cells'
properties of the 4 cells and 'unlock' them, and go into the 'format cells'
properties of the rest and lock them. Then I go to tools, protection,
protect sheet, I add a password and check only 'unlocked cells'. This isn't
doing it, I can still edit the sheet, I emailed it to someone else and he
can edit the sheet. What am I doing wrong? Thank you!

 

[Dave Peterson]

You could prompt for a password using an inputbox, but the user's entry will be
visible. You could design your own userform to mask the characters (if you
wanted).

See if these give you any ideas:
Option Explicit
Sub testme01()
Dim myPwd As String
Dim wks As Worksheet
Set wks = Worksheets("sheet1")
With wks
If .ProtectContents _
Or .ProtectDrawingObjects _
Or .ProtectScenarios Then
myPwd = InputBox(prompt:="What's the frequencey, Kenneth?", _
Title:="Getting Password")
On Error Resume Next
.Unprotect Password:=myPwd
If Err.Number <> 0 Then
MsgBox "Invalid password"
Err.Clear
End If
On Error GoTo 0
End If
End With
End Sub

Sub testme02()
Dim wks As Worksheet
Set wks = Worksheets("sheet1")
With wks
If .ProtectContents _
Or .ProtectDrawingObjects _
Or .ProtectScenarios Then
On Error Resume Next
Application.Dialogs(xlDialogProtectDocument).Show
If Err.Number <> 0 Then
MsgBox "Invalid password"
Err.Clear
End If
On Error GoTo 0
End If
End With
End Sub

The second one just shows the built in dialog. You may want just teach people
how to use Tools|Protection. It'll be something that they can use on other
workbooks, too.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm