Locking/Unlocking cells upon condition

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

On Sat, 10 Mar 2007 14:10:03 -0800, Brettjg
wrote:

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

Or as Jerry points out........use ThisWorksheet instead of Activesheet.


Gord

On Sat, 10 Mar 2007 15:36:35 -0800, Gord Dibben gorddibbATshawDOTca wrote:

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

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

"Gord Dibben" wrote:

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

On Sat, 10 Mar 2007 14:10:03 -0800, Brettjg
wrote:

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

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







"Brettjg" wrote:

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

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 he 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.
"Brettjg" wrote:

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

"Gord Dibben" wrote:

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

On Sat, 10 Mar 2007 14:10:03 -0800, Brettjg
wrote:

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

JERRY! YEEEEEEEEEEEEeeeeeeeeeeeeeeeeeeeeaaaaaaaaaaaaaaaaa hhhhhhhhhhhhh!

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 -

"Brettjg" wrote:

JERRY! YEEEEEEEEEEEEeeeeeeeeeeeeeeeeeeeeaaaaaaaaaaaaaaaaa hhhhhhhhhhhhh!

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

Jerry and Brett

You seem to have resolved the problem.

Brett.........for more information, Chip Pearson has good coverage on Events at
his site.

http://www.cpearson.com/excel/events.htm

Scroll down to see "enabling and disabling events"


Gord

On Sat, 10 Mar 2007 20:21:15 -0800, JLatham HelpFrom @
Jlathamsite.com.(removethis) wrote:

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 -

"Brettjg" wrote:

JERRY! YEEEEEEEEEEEEeeeeeeeeeeeeeeeeeeeeaaaaaaaaaaaaaaaaa hhhhhhhhhhhhh!

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