Locking cells

  • Thread starter Thread starter Duplatt
  • Start date Start date
D

Duplatt

Is it possible to lock and unlock cells on one sheet based on certain
criteria in a certain cell on another sheet ?
 
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
 
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
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
 
The code must be on a worksheet module.
Right click the tab for the sheet 5; select View Code
Do not type the sub, just copy and paste from my message (making sure there
are no unneeded line breaks)
best wishes
 
Bernard
I am making some headway
I did as you suggested - Copy/paste
I now get the Locked/unlocked Msxbox when date is changed.
However, the range on sheet3 can still be written to.
If I remove the single quote marks from Worksheets ("Sheet3").Protect and
UnProtect it will work one time . It protects sheet3 entirely, not just the
range.
Then when I go back to change the date again, I get "Runtime Error 1004.
Unable to set the Locked Property of the range class"
Duane
 
Try this revised 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
With Worksheets("Sheet3")
.Unprotect
.Cells.Locked = False
.Range("B1:H10").Locked = True
.Protect
End With
MsgBox "locked"
Else
Worksheets("Sheet3").Unprotect
MsgBox "unlocked"
End If
End If
End Sub


Gord Dibben MS Excel MVP
 
Back
Top