Locking/Unlocking cells upon condition

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have put the following code into the worksheet code but it doesn't work.
Can someone put me straight please?

Private Sub PERSONAL_LOCK()
If Range("add.years.1").Value < 3 Then
ActiveSheet.UNPROTECT
ActiveSheet.Range("add.prv.1:add.prv.yrs.2").Locked = False
ActiveSheet.Protect
End If
End Sub

Thanks, Brett
 
Go back and create another named range that includes the cell(s) now referred
to as add.prv.1 and add.priv.yrs.2 and then revise your .Locked statement.

Lets say that add.prv.1 refers to A1 and add.priv.yrs.2 refers to B1. You
could select A1 and B1 and give them a name like add.priv.yrs.all then use
ActiveSheet.Range("add.priv.yrs.all").Locked = False

An alternative is to break the statement into two statements:
ActiveSheet.Range("add.priv.yrs.1").Locked = False
ActiveSheet.Range("add.priv.yrs.2").Locked = False
 
Hi JL, I've changed the code to this, but it still doesn't work. This is the
only code in the sheet code, nothing else at all. Am I missing some other
code that it needs? Currently the value of "add.years.1" is 1 so the cells
should be unlocked, but they are still locked.

Private Sub PERSONAL_LOCK()
If Range("add.years.1").Value >= 3 Then
ActiveSheet.UNPROTECT
ActiveSheet.Range("add.prv.1").Locked = True
ActiveSheet.Range("add.prv.yrs.1").Locked = True
ActiveSheet.Range("add.prv.2").Locked = True
ActiveSheet.Range("add.prv.yrs.2").Locked = True
ActiveSheet.Protect
End If
End Sub
 
You say "sheet code" - so I am presuming you want this to work automatically
and detect when add.years.1 goes >= 3

Then in the sheet's code you need to use the _Calculate() event processor to
do this for you most likely, and I am also presuming you may want to toggle
the .Locked state of those cells, so here's what code to work that way could
look like:

Private Sub Worksheet_Calculate()
ActiveSheet.Unprotect
If Range("add.years.1").Value < 3 Then
ActiveSheet.Range("add.prv.yrs.1").Locked = False
ActiveSheet.Range("add.prv.yrs.2").Locked = False
Else
'>= 3 processing
ActiveSheet.Range("add.prv.yrs.1").Locked = True
ActiveSheet.Range("add.prv.yrs.2").Locked = True
End If
ActiveSheet.Protect
End Sub

If your worksheet is protected with a password, then you'll need to provide
the password in the code, similiar to this:

Private Sub Worksheet_Calculate()
ActiveSheet.Unprotect Password:="mypassword"
If Range("add.years.1").Value < 3 Then
ActiveSheet.Range("add.prv.yrs.1").Locked = False
ActiveSheet.Range("add.prv.yrs.2").Locked = False
Else
'>= 3 processing
ActiveSheet.Range("add.prv.yrs.1").Locked = True
ActiveSheet.Range("add.prv.yrs.2").Locked = True
End If
ActiveSheet.Protect Password:="mypassword"
End Sub
 
Crackajack JL, it works beautifully. Thank you so much for your help. I also
tried another If after the first one for a different test and range and that
was successful as well. Do I presume that anything I want to do like this has
to be in the same Private Sub which must be called Worksheet_Calculate?
 
You can have multiple event handlers in a sheet module, but not multiples of one
type of event.

i.e. one Worksheet_Calculate event per sheet.

That one event could call various macros or move to statements based on the
value of a cell or cells. Or just expand upon the conditions in Jerry's code.

e.g. using Select Case method to choose what to do when a condition is met with
your Worksheet_Calculate event.

Example of Select Case code..........

Private Sub Worksheet_Calculate()
Dim Target As Range
For Each Target In Me.Range("A1:A100")
With Target
Select Case .Value
Case Is = 3: mymacro3
Case Is = 1: mymacro1
Case Is = 2: mymacro2
Case Is = 4: mymacro4
'etc.
End Select
End With
Next Target
End Sub


Gord Dibben MS Excel MVP
 
Hey Gord, a little more help if you would. The workbook has three sheets, and
only the sheet 'PERSONAL' has sheetcode. All the locking/unlocking etc works
in that sheet, and I then unprotect it to break links and a few other things
(being careful to unprotect it before each break etc). The I activate the
sheet 'NEW LIABILITIES' and execute the following code:

'COPY "LIABILITIES" FORMULAS
Sheets("NEW LIABILITIES").Select
Sheets("NEW LIABILITIES").Activate (this is probably unnecessary)
Rows("60:238").Delete (this works and happens)
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollRow = 10
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("J21").Select

Now it does all of this except when it gets to protecting the sheet, when it
trips over because of the code in the 'PERSONAL' sheet re locking cells in
'PERSONAL'. I don't understand why the code in 'PERSONAL' should have any
effect on what I do in 'NEW LIABILITIES'

This is the sheetcode for 'PERSONAL':
Private Sub Worksheet_Calculate()
ActiveSheet.UNPROTECT

If Range("add.years.1").Value >= 3 Then
ActiveSheet.Range("previous.address.1").Locked = True
Else
'< 3 processing
ActiveSheet.Range("previous.address.1").Locked = False
End If

If Range("yrs.position.1").Value >= 3 Then
ActiveSheet.Range("previous.job.1").Locked = True
Else
'< 3 processing
ActiveSheet.Range("previous.job.1").Locked = False
End If
ActiveSheet.Protect
End Sub

It trips over when trying to protect 'LIABILITIES' and debugs to the line
ActiveSheet.Range("previous.address.1").Locked = False
from the sheetcode in 'PERSONAL'.

EH?
Thanks for your help, Brett
 
Glad you got going, and happy to see that Gord Dibben had my back during my
absence again.

Be careful of what sheet you are referencing when inside one of the
worksheet event handlers. A couple of things I see here that might need
attention to:

under your 'COPY "LIABILITIES" FORMULAS line you select and activate sheet
NEW LIABILITIES and do some work on it but you don't unprotect it
specifically before doing the work - even though you say things are working.

When it errors out in that section, as I presume it is, if you hit the
[Debug] button, which line of code is shaded (which indicates where the error
is at). Is it the .Protect statement or the Range("J21").Select statement.
If it's the .Select statement, the problem is most likely that J21 is Locked
and the protection is set to not permit selecting Locked cells on protected
sheets.

You are correct, protecting/unprotecting any specific sheet should NOT
affect the protected/unprotected state of any other sheet in the workbook. I
will suggest a change to the code in the PERSONAL Worksheet_Calculate() event
- change all instances of ActiveSheet. to ThisWorksheet.

I'm a little concerned at this point because you say that the code you show
is for the PERSONAL sheet, not the NEW LIABILITIES sheet. Was that a typo
somewhere?

If you are in any worksheet event handling code and need to do something
with ranges in other worksheets, you must specifically point to them. For
example, if you are in the Worksheet_Calculate() event for the PERSONAL
worksheet and wish to examine/manipulate ranges on the NEW LIABILITIES sheet,
then in the code you would need to reference them this way:
Worksheets("NEW LIABILITIES").Range("previous.address.1").Locked = True

So in a worksheet event handler, ActiveSheet. and/or ThisWorksheet. refer to
the sheet that the code resides in, and if you need to reference another
sheet in the workbook in that code, you must specify the sheet name.

Hope that helps clear things up some.

As Gord said earlier, there are several events for a worksheet that can be
used for various things. Look at the code and view the pull-down list at the
right side of the top of the code module window for a list of them (when
Worksheet is displayed in the pull-down list just to it's left). Similarly,
there are events that are associated with a workbook, such as BeforeSave and
BeforeClose, Activate, Deactivate and more - just like each worksheet, this
is a special module associated with the workbook vs worksheets and/or general
purpose code.
 
Is the code you posted a macro that you run manually on the NEW LIABILITIES
sheet?

Post the entire code between Sub mymacro() and End Sub

Don't forget that you have Worksheet_Calculate event code in PERSONAL sheet
which will be firing if the application calculates.

That code is looking for the named ranges which don't exist because the
activesheet is now NEW LIABILITIES.

I am not sure how to overcome that problem other than to turn calculation to
manual when you switch sheets.


Gord
 
Hi Jerry, I think my head hurts. I'll explain the sequence of events.
1. I open up the file in it's master state (no client data).
2. I enter the client name which creates paths for the folder and file to
follow later
3. I click a button to select one of six macros to run, depending on client
type
4. The macro from 3. (above) performs varios functions on the three sheets
in the book to get into the state I require for the client type (different
formulas, deletes rows etc)
5. I then save the file in the client's folder and email it out to them so
that they can enter data and send it back to me.

The lock/unlock cells routine is so that the client can't enter unnecessary
data (e.g if at the same address for 3 years I don't need the previous
address and so the cells for the previous address get locked). This of course
has to be in the sheet code for 'PERSONAL' which is now as follows:

Private Sub Worksheet_Calculate()
ActiveSheet.UNPROTECT

If Range("add.years.1").Value >= 3 Then
Range("previous.address.1").Locked = True
Else
'< 3 processing
Range("previous.address.1").Locked = False
' IT TRIPS UP ON THE LINE ABOVE when I hit the macro button

End If

If Range("yrs.position.1").Value >= 3 Then
Range("previous.job.1").Locked = True
Else
'< 3 processing
Range("previous.job.1").Locked = False
End If

If Range("nm.first.2").Value < 1 Then
Range("client.2.1").Locked = True
Range("client.2.2").Locked = True
Else
'< 3 processing
Range("client.2.1").Locked = False
Range("client.2.2").Locked = False
ActiveWindow.ScrollRow = 9

If Range("add.years.2").Value >= 3 Then
Range("previous.address.2").Locked = True
Else
'< 3 processing
Range("previous.address.2").Locked = False
End If

If Range("yrs.position.2").Value >= 3 Then
Range("previous.job.2").Locked = True
Else
'< 3 processing
Range("previous.job.2").Locked = False
End If
End If

ActiveSheet.Protect
End Sub

You will note that I have removed Activesheet altogether, and not replaced
it with ThisWorksheet. It seems unnecessary, and it does work like this. It
did NOT like having ThisWorksheet.UNPROTECT as the second line (it didn't
seem to know what I meant).

So far all that works when I pretend to be the client filling in data, but
this step actually comes after I hit the macro button to set up the client
type. The code for that particular button is as follows:

Sub NEW_CLIENT()
Sheets("Instructions").Visible = True
Sheets("Privacy Act").Visible = True
Sheets("PERSONAL").Select
ActiveSheet.UNPROTECT
Range("virgin").FormulaR1C1 = ""

Application.DisplayAlerts = False
ActiveSheet.UNPROTECT
ActiveWorkbook.BreakLink Name:=Range("Falsepath.97PREVIOUS"),
Type:=xlExcelLinks
ActiveSheet.UNPROTECT
ActiveWorkbook.BreakLink Name:=Range("Path.DATAFILE"), Type:=xlExcelLinks
ActiveSheet.UNPROTECT
ActiveWorkbook.BreakLink Name:=Range("Falsepath.SNAPSHOT"),
Type:=xlExcelLinks
ActiveSheet.UNPROTECT
ActiveWorkbook.BreakLink Name:=Range("Path.DD"), Type:=xlExcelLinks
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.DisplayAlerts = True

'COPY "LIABILITIES" FORMULAS
Sheets("NEW LIABILITIES").Select
Sheets("NEW LIABILITIES").Activate
Rows("60:238").Delete
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollRow = 10
'IT WORKS FINE UNTIL THE FOLLOWING LINE:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("J21").Select
BLAH BLAH BLAH to End Sub

When it gets to the ActiveSheet.Protect it fails , but it debugs to the
Private Sub in 'PERSONAL' even though its on the 'NEW LIABILITIES ' sheet (I
have marked as "IT TRIPS UP ON THE LINE ABOVE". There is no other sheet code
in any other sheet except 'PERSONAL'

I know that's a lot to digest for you. In other words I'm executing a macro
that tootles off to another sheet, does most of it's stuff, but fails when it
tries to protect that sheet (not 'PERSONAL'), but it debugs to the sheetcode
in 'PERSONAL'. I have changed the Activesheet to be 'NEW LIABILITIES' as you
can see.

Jerry, your help is greatly appreciated.
Regards, Brett
 
Hey Jerry, the other thing is: Can ThisWorksheet be used to refer to more
than one worksheet. That is if 'PERSONAL' is active I can refer to it as
ThisWorksheet, but can I then activate 'NEW LIABLITIES' and refer to that as
ThisWorksheet as well?

Thanks, Brett
 
Hi Gord, thanks for your help here. I've posted back to Jerry with a full
explanation of the sequence of events for better understanding. I've been
pusuing the ThisWorksheet idea, but the Private Sub didn't like it.

Regards, Brett
 
ThisWorksheet can refer to more than one worksheet, but only one sheet at a
time. It's all related to what Gord explained earlier.

You have Event processing code for a worksheet - typically you only do
things to that worksheet while in it's event code, although you may do
others. But while in any given sheet's event code, ThisWorksheet will refer
to the sheet that the code is associated with. And it should remain that way
even if another sheet is activated for some reason and the code still has
work to do. Most of the time ActiveSheet will also refer to that same sheet
while in one of the sheet's event processors - but that's not always
guaranteed. If you get in the middle of a module for PERSONAL and use a
command such as Worksheets("NEW LIABILITIES").Activate then ActiveSheet will
refer to NEW LIABILITIES while ThisWorksheet would still refer to PERSONAL.
If you switch sheets in the middle of one of these and then try to reference
a range that on the sheet with code running without specifying which sheet
(or the wrong sheet) then you'll error out.

I'm wondering if we haven't started down the wrong path for this, and the
aggregate changes throughout the workbook aren't really ripping at our
throats.

From reading the explanation 2 posts up, I see you really don't need any of
this to happen until you're done messing with the workbook and ready to email
it to your client.

I suggest we just get rid of all of this Worksheet_... stuff and do your
processing in one of the WORKBOOK related events, such as
Workbook_BeforeSave()

In that area there is no such thing as ThisWorksheet, and you'll want to
stay away from ActiveSheet also; sticking with explicitly naming the sheets
and the ranges on them that are to be altered/protected.

Also, in there you'll probably want to start off the code with
Application.EnableEvents = FALSE
and end it with
Application.EnableEvents = TRUE

That prevents the changes you'll make in the code from triggering other
events, like the Worksheet_Change() etc.

To get to the Workbook equivalent of the Worksheet code modules, easy way is
to right click on the Excel Icon immediately to the left of the File option
in the menu bar and choose [View Code] from it's list.

I'm going to put up untested code here to give you an idea of what it could
look like. Notice that I've used With statements referring to various
worksheets - change the names to be correct with the specific ranges used in
the code between the various With ... End With pairings.

Also, note that the properties involved are all preceded with a dot, like
..Range(...) instead of just Range(...) That's because we are using
With...End With which keeps us from having to type
Worksheet('somesheetname').Range(...) over and over. If I missed one, there
should NOT be any ActiveSheet. or ThisWorksheet. references in this code at
all:

Might want to make a new copy of the workbook, go in and just delete all of
the Worksheet_Change() event handling for all sheets (select and delete, that
simple) and put this in the Workbook_BeforeSave() event and see if that
doesn't serve you better.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.EnableEvents = False

With Worksheets("PERSONAL") ' change name appropriately
.Unprotect
If .Range("add.years.1").Value < 3 Then
.Range("add.prv.yrs.1").Locked = False
.Range("add.prv.yrs.2").Locked = False
Else
'>= 3 processing
.Range("add.prv.yrs.1").Locked = True
.Range("add.prv.yrs.2").Locked = True
End If
.Protect
End With

With Worksheets("NEW LIABILITIES") ' change name appropriately
If .Range("yrs.position.1").Value >= 3 Then
.Range("previous.job.1").Locked = True
Else
'< 3 processing
.Range("previous.job.1").Locked = False
End If
End With

With Worksheets("NEW CLIENT") ' change name appropriately
If Range("nm.first.2").Value < 1 Then
.Range("client.2.1").Locked = True
.Range("client.2.2").Locked = True
Else
'< 3 processing
.Range("client.2.1").Locked = False
.Range("client.2.2").Locked = False

If Range("add.years.2").Value >= 3 Then
.Range("previous.address.2").Locked = True
Else
'< 3 processing
.Range("previous.address.2").Locked = False
End If

If Range("yrs.position.2").Value >= 3 Then
.Range("previous.job.2").Locked = True
Else
'< 3 processing
.Range("previous.job.2").Locked = False
End If
End If
End With
Application.EnableEvents = True
End Sub
 
See my new suggestion above - after your last explanation, I'm thinking you
don't need to worry with this until you're finished working with the file and
are ready to send it off to the client. In which case, we can probably get
away from the confusion caused by multiple sheets trying to deal with
Change() seemingly simultaneously - and do the coding as a final step before
the file is saved using the Workbook_BeforeSave(...) event.

I'm not proud - I know when to retreat when an idea seems to have not panned
out. It often happens around here: you get one set of specs and come up with
a solution only to find there's a "Rest of the Story" waiting. No big deal -
it happens in more cases than it doesn't. Hope this helps some, and my
thanks to Gord for jumping in with his good ideas also (I learn a lot when he
does that) - and maybe he'll have a different one than my track-jumping idea
to deal with this also.
 
JERRY! YEEEEEEEEEEEEeeeeeeeeeeeeeeeeeeeeaaaaaaaaaaaaaaaaahhhhhhhhhhhhh!

In the end there's no ThisWorksheet, no Activesheet, just specific names.
I'm using the original code again, but like this. What a slog. Thanks very
much for your help and persistance.

Brett
 
I know I speak for myself, and I think maybe for Gord also when I say

Thank you for the Thank you.

All's well that ends well, and it was probably a learning experience for all
concerned. Sometimes you just have to turn around, make three left turns
instead of a single right turn to get to the end of the block -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Locking cells based on value in another cell 2
lock cell 6
VB Coding 1
Conditional locking of cells for individual rows 4
locked cell not locked 1
Excel locking cell based on value in another cell 1
loop through rows 1
macro to unlock and lock cells in password protected sheet 3

Back
Top