Google Answers Logo
View Question
 
Q: Conditional lock of cells in excel ( Answered 5 out of 5 stars,   0 Comments )
Question  
Subject: Conditional lock of cells in excel
Category: Computers
Asked by: uniri-ga
List Price: $10.00
Posted: 08 Jun 2006 11:40 PDT
Expires: 08 Jul 2006 11:40 PDT
Question ID: 736450
Hello,
Would you please advice of a way to do the following in Excel:
My workbook includes 8 worksheets. I need to lock or unlock certain
same cells on five different sheets based on whether a different cell,
located in a different worksheet is blank or not.

More specifically:
If cell A1 on Sheet1 includes a value:
If True then lock cells C1:C10 on Sheet2, Sheet3, Sheet4, Sheet5,
Sheet6 and calculate a formula that uses the value in cell A1 sheet1.
For example:
Cell C1 on Sheet2 = (Cell A1 on Sheet1+1) * (Cell B1 on Sheet 2).
Cell C2 on Sheet2 = (Cell A1 on Sheet1+1) * (Cell B2 on Sheet 2).
And so on for cells C3:C10. 
On sheet3 the formulas will be:
Cell C1 on Sheet3 = (Cell A1 on Sheet1+1) * (Cell B1 on Sheet3)?.
Same for Sheet4, Sheet5 and Sheet6.

If the condition is False (cell A1 on Sheet1 is blank), then unlock
cells C1:C10 on Sheet2 through Sheet6 and leave cells C1:C10 on each
of these sheets blank.

Notes: 
1. The start point is that the spreadsheet includes a value in cell A1
Sheet1. The user may choose to delete the value and leave the cell
blank or to enter a different value (Zero is considered as a value and
not as blank).
2. I don?t know that it matters but I named the Sheet?s tabs and also
cell A1 on sheet1 has a defined name which is used in the formula.


Many thanks for your help!!!
Answer  
Subject: Re: Conditional lock of cells in excel
Answered By: maniac-ga on 10 Jun 2006 09:39 PDT
Rated:5 out of 5 stars
 
Hello Uniri,

Visual basic code that should do as you asked is included at the end
of this answer. This code is written assuming:
 - Worksheet Sheets2 through Sheet6 are protected
 - the 8th worksheet (I don't know its name) is unchanged
 - if the user must change B1 through B10 on each sheet, those cells
will be unlocked separately from the macros
 - the macro should leave the user on Sheet1 when a change is made on that sheet
If one of these assumptions are incorrect, please make a clarification
request indicating the correct assumption so I can fix the macro.

To add the macros to your worksheet, you will need to do the following:

[0] Open the worksheet (or have it already open).

[1] Use the menu
  Tools -> Macro -> Visual Basic Editor
to bring up visual basic. At this point, you should have a "projects
window" in the upper right.

[2] Right click on Sheet1 to bring up a menu that includes "View
Code", select that item to bring up a code window with Sheet1 as part
of its title. Copy and paste the Worksheet_Change macro into that
window.

[3] Use the menu
  Insert -> Module
to bring up another code window. Copy & paste the Lockem and Unlockem
macros into that window.

You can make any changes you find necessary at this point [see below
for suggestions]. Use the menu "Quit and Return to Microsoft Excel" to
return to the spreadsheet.

At this point, the macros should work as expected.

If you need to make some changes, here are some ideas on how to implement them.

[1] Revise the number of sheets updated - change the numbers in the
FOR loops in Lockem and Unlockem. For example, if you need six instead
of five sheets changed, change the code to read
  FOR I = 2 to 7

[2] Revise the formula - change the value within the string. Note the
use of both relative (C[-1]) and absolute (C1) cell references. For
example, there is something slightly inconsistent in your question
where you say
  calculate a formula that uses the value in cell A1 sheet1
and then
  Cell C1 on Sheet2 = (Cell A1 on Sheet1+1) * (Cell B1 on Sheet 2)
I assumed the latter was the correct statement and the code reflects
that. If that is wrong, change
        ActiveCell.FormulaR1C1 = "=RC[-1]*'Sheet1+1'!R1C1"
to read
        ActiveCell.FormulaR1C1 = "=RC[-1]*'Sheet1'!R1C1"

[3] If you add an additional "active" cell on Sheet1, add conditions
similar to those currently in Worksheet_Change to determine when to
act and the actions to perform.

Please make a clarification request if any part of the answer is
incorrect or unclear. I would be glad to correct the answer as needed.
Good luck with your work.

  --Maniac

The subroutine that activates with each worksheet change in Sheet1.
This must be put directly into the sheet1 module as described above.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    ' Only activate if change is of our special value
    If (Intersect(Target, Range("A1")) Is Nothing) Or _
        (Target.Cells.Count > 1) Then Exit Sub
    
    If Range("A1").Value = "" Then
        Call Unlockem
    Else
        Call Lockem
    End If
End Sub


The two subroutines that lock / unlock the cells. These can go into a
module by themselves.

Sub Lockem()
'
' Lockem Macro
' Macro recorded 6/10/2006 by Maniac
'

'
    For I = 2 To 6
        A$ = "Sheet" + Format(I)
        Sheets(A$).Select
        ActiveSheet.Unprotect
        Range("C1").Select
        ActiveCell.FormulaR1C1 = "=RC[-1]*'Sheet1+1'!R1C1"
        Range("C1:C10").Select
        Selection.FillDown
        Selection.Locked = True
        ActiveSheet.Protect
    Next I
    Sheets("Sheet1").Select
End Sub
Sub Unlockem()
'
' Unlockem Macro
' Macro created 6/10/2006 by Maniac
'

'
    For I = 2 To 6
        A$ = "Sheet" + Format(I)
        Sheets(A$).Select
        ActiveSheet.Unprotect
        Range("C1").Select
        ActiveCell.FormulaR1C1 = ""
        Range("C1:C10").Select
        Selection.FillDown
        Selection.Locked = False
        ActiveSheet.Protect
    Next I
    Sheets("Sheet1").Select
End Sub

Request for Answer Clarification by uniri-ga on 12 Jun 2006 20:01 PDT
Dear Maniac,
Thanks so much! The macro works well, I adjusted it to fit my
spreadsheet and it locks and unlocks the cells when needed.
I have just one problem, when I open the spreadsheet I get a message
box that asks me to reduce the security level in order to activate the
macro. The message also says that ?a digitally signed Trusted
Certificate is not attached to the macros.?

Since this spreadsheet will be used by many different users (some are
not that familiar with excel) this becomes a problem.

When I reduce the level of security on my computer to medium, I?m
being asked to enable the macros each time the spreadsheet opens. This
is one other thing I would like to avoid.

What I?m trying to accomplish is that the user will not get any
messages regarding the macros. Is that possible?

Thanks again!

Clarification of Answer by maniac-ga on 13 Jun 2006 05:23 PDT
Hello Uniri,

The changes to macro security in Excel (as well as Word and
Powerpoint) were done to stop the spread of macro viruses a few years
ago. You've already found the primary work around I use (to set your
security level to medium).

There is however an alternative, which is to create your own security
certificiate and have yourself as a "publisher" be trusted on all the
systems you administer. I haven't worked through this myself, but
there appear to be several good explanations of this process at the
following sites. Note that adding a certificate will also help confirm
the macro has not been modified.

A user's group with several good links to programs and the "how to"
steps to follow at
  http://www.excelsig.org/Tips/0409_digital_certificates.htm
some of these are low or no cost. One (using a Verisign certificate)
does cost some money but may be an alternative you want to pursue.

Some technical references at Microsoft:
  http://support.microsoft.com/default.aspx?scid=kb;en-us;308983
  http://support.microsoft.com/support/kb/articles/Q206/6/37.ASP
  http://support.microsoft.com/?kbid=262876
  http://office.microsoft.com/assistance/9798/signturs.aspx

An explanation of the process at Verisign
  http://www.verisign.com/static/030996.pdf

There are several other references as well. Search with a phrase such as
  excel macro digitally signed trusted certificate
  excel macro digitally signed trusted certificate site:microsoft.com
to find out more information.

Good luck.
  --Maniac

Request for Answer Clarification by uniri-ga on 13 Jun 2006 20:28 PDT
Dear Maniac,
Thank you very much for the information. I will look into this.

Meanwhile I have notice a different problem that you may be able to fix:
Once the macro is activated the cursor is placed on the range C1:C10
on each of sheets 2 through 6 (regardless of if those cells are locked
or not).
Is it possible to modify the macro so that the cursor will be located
on a certain different cell on those sheets (2 through 6) - for
example cell D5? And also on a specific cell on sheet 1, for example
F6.
I appreciate your help very much.
Thanks you!!!

Clarification of Answer by maniac-ga on 14 Jun 2006 06:48 PDT
Hello Uniri,

It is straight forward to change the current selection in a sheet. The
code has a few examples of this already or you could find it through
the Visual Basic help (search for select or selection). The code for
the specific cases you've asked for follow.

> Is it possible to modify the macro so that the cursor will be located
> on a certain different cell on those sheets (2 through 6) - for
> example cell D5?
Before the Next I statement in Lockem and Unlockem, add
  Range("D5").Select

> And also on a specific cell on sheet 1, for example F6.
Before the End Sub statement in Worksheet_Change, add
  Range("F6").Select
[Sheet1 was already selected as the last action in both Lockem and
Unlockem. This statement could go into those two places as well.]

As a side note, I also find the "object browser" in Visual Basic to be
extremely helpful in finding out how to do things in VB. When in
Visual Basic, use the menu
  View -> Object Browser
to bring it up. Enter a phrase in the box in the upper left to bring
up a list of matching items. When I did this with
  Select
it brought up a pretty extensive list of "select" and "selection"
items. You can then scroll down to
  Excel | Range | Select
and select that line. The lower part of the window will show the other
functions and properties of a Range. At this point you can press the
question mark in the upper right (?) to go directly to the help on the
selected item. On my system, it gave me a brief explanation of a range
selection, links to examples, other references, and refers to an
alternative named "Activate".

I hope this helps.
  --Maniac

Request for Answer Clarification by uniri-ga on 14 Jun 2006 20:03 PDT
Thank you so much Maniac! You've provided a great help.
I'll be sure to try the object browser in the future, it sounds very useful.

Clarification of Answer by maniac-ga on 15 Jun 2006 04:37 PDT
Hello Uniri,

Thanks for the kind words and good luck with your work.

  --Maniac
uniri-ga rated this answer:5 out of 5 stars and gave an additional tip of: $5.00

Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy