Mr. Gates sent me to check up on Frank <vbg>. By the way, Bill (as I like to
call him) says "hi, Frank".
Say you want to lock cells in column A that get bigger than 100 as the result of
typing. (Formula cells on your worksheet should already be locked, right???)
Right click on the worksheet tab that should behave this way. Select View
Code. Paste this into the code window.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myPWD As String
myPWD = "hi"
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If Me.ProtectContents = False Then Exit Sub
On Error GoTo errHandler:
With Target
If IsNumeric(.Value) Then
If .Value > 100 Then
Me.Unprotect Password:=myPWD
.Locked = True
Me.Protect Password:=myPWD
End If
End If
End With
errHandler:
Me.Protect Password:=myPWD
End Sub
Don't forget to lock the VBA Project, too. Else you'll have inquisitive types
looking at your code and seeing the password.
Inside the VBE, you can lock the project.
Tools|VBAProject Properties|Protection tab.
Give it a memorable password and lock the project for viewing.
That said, worksheet protection is very weak. There's code posted here every
day/week that would unprotect the worksheet.
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
=======
And since you're not going to use conditional formatting for this, you can
indicate whether the cell is locked:
Select your range and use a formula is:
=CELL("protect",A1)
where A1 is the activecell.
(give it a nice pattern and see what happens.)
It makes seeing the results of testing easier, too.
