View Question
Q: Conditional lock of cells in excel ( Answered ,   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!!!```
 Subject: Re: Conditional lock of cells in excel Answered By: maniac-ga on 10 Jun 2006 09:39 PDT Rated:
 ```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```

 There are no comments at this time.