Bernard
Thank you again for your assistance.
I may not know enough about macro's to make this work.
I opened sheet5 - clicked on Tools/macro/VB Editor and typed in the code
I didn't know what else to do so I opened sheet3 and tried the exercise. It
did not work. I have double checked the code and it is entered as you wrote
it.
However, I found that by referencing cell G21 ( =sheet5!g21) in an obscure
cell on sheet3 I can use Validation (custom) to solve my problem.
Any thoughts on the VB solution will be appreciated though.
Duane
"Bernard Liengme" wrote:

This sub needs to be placed as a WorkSHEET module for Sheet5
I have comment out the protection steps - you can manually protect the work
sheet as needed, or just remove the single quote that makes the VBA line a
comment. Likewise add a single quote in front of Msgbox lines to make them
comments (I use this to test my code)

You may get an error the first time the code tries to lock the cells if they
are already locked.

It will work on other computers because the code is part of the workbook.
People sometime have put code in a special file (PERSONAL.XLS) and that code
will be seen only on the computer that is home to PERSONAL.XLS.

You need to be aware what some people have Excel set in a way that prevents
macros from running. You should warn the people who will get the file that
you have a macro - hopefully they are under you in the organizational chart
so you can 'boss' them!

To learn more about VBA see David McRitchie's site on "getting started" with
VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("G21")) Is Nothing Then
If Range("G21").Value < 2002 Then
Worksheets("Sheet3").Range("B1:H10").Locked = True
' Worksheets("Sheet3").Protect
MsgBox "locked"
Else
Worksheets("Sheet3").Range("B1:H10").Locked = False
' Worksheets("Sheet3").UnProtect
MsgBox "unlocked"
End If
End If
End Sub


best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Duplatt" wrote in message
...
I would like to try, with your assistance.
My goal is to Lock cells $B$3:$AP$22 on Sheet3 if Sheet5!$G$21 < 2002 ,
else unlock.
This spreadsheet will be distributed to others. Will the VBA code transfer
to other computers, with Excel, when sent by e-mail ?
Thank You -- Duane Platt


"Bernard Liengme" wrote:

If you are prepared to use VBA
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Duplatt" wrote in message
...
Is it possible to lock and unlock cells on one sheet based on certain
criteria in a certain cell on another sheet ?